using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;

using Microsoft.VisualBasic;

namespace Raptor.Client
{
  public partial class frmMain : Form
  {
    #region Class Variables & Properties

    #region Variables
    int iSelectedTablesCount = 0;
    bool reloadingForm = false;

    private string m_PrimaryKeyColName;

    private string m_PrimaryKeyType;

    private string m_ActiveColName;

    private string m_TableName;

    private bool m_DynamicQueries;

    private string m_SearchResultsLimit;

    private bool m_Approved;

    private bool m_PromptOverwrite = true;

    SqlConnection PRMSSQLConn;

    SqlConnection RaptorSQLConn;

    string[] sExecutionPlan = new string[17];

    GenOptions SystemOptions = new GenOptions();

    AppSettings thisSettings = new AppSettings();

    private List<List<String>> m_DOOptions = new List<List<string>>();

    private List<List<String>> m_EmbededObjectList = new List<List<String>>();

    DataTable sourceTables = null, sourceColumns = null;

    private List<List<String>> m_SelectedTableColumns;

    private List<List<String>> m_SelectedSearchResults;

    //private List<List<List<String>>> m_SelectedObjects = new List<List<List<String>>>();

    private Dictionary<string, List<List<List<String>>>> SelectionSetting = new Dictionary<string, List<List<List<string>>>>();

    private long m_GenerateObject_ID;

    //private List<List<String>> m_PrevioslyGeneratedDOOptions = new List<List<string>>();

    //private List<List<String>> m_PrevioslyGeneratedEmbedObjects = new List<List<string>>();

    //private List<List<String>> m_PrevioslyGeneratedSearchParams = new List<List<string>>();

    //private List<List<String>> m_PrevioslyGeneratedSearchResults = new List<List<string>>();

    private Dictionary<string, List<List<List<String>>>> PreviouslyGeneratedSettings = new Dictionary<string, List<List<List<string>>>>();

    private string m_StoredProcName;

    public string StoredProcName
    {
      get { return m_StoredProcName; }
      set { m_StoredProcName = value; }
    
    }

    #endregion

    #region properties
    public List<List<String>> DOOptions
    {
      get { return m_DOOptions; }
      set { m_DOOptions = value; }
    }

    public bool Approved
    {
      get { return m_Approved; }
      set 
      { 
        m_Approved = value;
        if (this.m_Approved)
        {
          btnGenerate.Enabled = true;
        }
      }
    }

    public List<List<String>> selectedTableColumns
    {
      get { return m_SelectedTableColumns; }
      set { m_SelectedTableColumns = value; }
    }

    public List<List<String>> selectedSearchResults
    {
      get { return m_SelectedSearchResults; }
      set { m_SelectedSearchResults = value; }
    }

    public List<List<String>> EmbededObjectList
    {
      get { return m_EmbededObjectList; }
      set { m_EmbededObjectList = value; }
    }

    //public List<List<String>> PrevioslyGeneratedDOOptions
    //{
    //  get { return m_PrevioslyGeneratedDOOptions; }
    //  set { m_PrevioslyGeneratedDOOptions = value; }
    //}

    //public List<List<String>> PrevioslyGeneratedEmbedObjects
    //{
    //  get { return m_PrevioslyGeneratedEmbedObjects; }
    //  set { m_PrevioslyGeneratedEmbedObjects = value; }
    //}

    //public List<List<String>> PrevioslyGeneratedSearchParams
    //{
    //  get { return m_PrevioslyGeneratedSearchParams; }
    //  set { m_PrevioslyGeneratedSearchParams = value; }
    //}

    //public List<List<String>> PrevioslyGeneratedSearchResults
    //{
    //  get { return m_PrevioslyGeneratedSearchResults; }
    //  set { m_PrevioslyGeneratedSearchResults = value; }
    //}

    #endregion

    #endregion

    #region Class Contructor
    public frmMain()
    {
      InitializeComponent();
    }
    #endregion

    #region Class Events
    private void frmMain_Load(object sender, EventArgs e)
    {
      LoadSettings();
    }

    public void btnLoadDataStructureClick(object sender, EventArgs e)
    {
      switch (CheckSettings())
      {
        case 0:
          {
            lblTableListInfo.Visible = true;
            lblTableListInfo.Refresh();
            lblTableCounter.Visible = true;
            progressBarTables.Visible = true;

            LoadDBTable();

            lblTableCounter.Visible = false;
            progressBarTables.Visible = false;
            flowLayoutPanel1.Visible = true;
            btnPreview.Visible = true;
            btnGenerate.Visible = true;
            btnReGenerate.Visible = true;
            break;
          }
        case 1:
          {
            MessageBox.Show("Your Save File settings have not been saved. You have to specify and save these settings before you can continue!", "Oops", MessageBoxButtons.OK);
            tbMain.Enabled = true;
            tbMain.SelectedIndex = 7;
            tabSubSettings.SelectedIndex = 0;

            break;
          }
        case 2:
          {
            MessageBox.Show("Your Database settings have not been saved. You have to specify and save these settings before you can continue!", "Oops", MessageBoxButtons.OK);
            tbMain.Enabled = true;
            tbMain.SelectedIndex = 7;
            tabSubSettings.SelectedIndex = 1;

            break;
          }
        case 3:
          {
            MessageBox.Show("Your Namespace settings have not been saved. You have to specify and save these settings before you can continue!", "Oops", MessageBoxButtons.OK);
            tbMain.Enabled = true;
            tbMain.SelectedIndex = 7;
            tabSubSettings.SelectedIndex = 2;

            break;
          }
      }

    }

    private void btnGenerate_Click(object sender, EventArgs e)
    {
      GrdTablesGenerated.Rows.Clear();

      foreach (KeyValuePair<string, List<List<List<string>>>> table in this.SelectionSetting)
      {
        ClearTextFields();
        
        m_PrimaryKeyColName = "";
        m_ActiveColName = "";
        m_TableName = table.Key;
        int iSettingsCounter = 0;
        // Set Environment Variables
        foreach (List<List<String>> tableColumns in this.SelectionSetting[table.Key])
        {
          switch (iSettingsCounter)
          {
            case 0:
              {
                SystemOptions.CurrentDOOptions = tableColumns;

                for (int iDOOptionIdx = 0; iDOOptionIdx < tableColumns[0].Count; iDOOptionIdx++)
                {
                  switch (tableColumns[0][iDOOptionIdx])
                  {
                    case "CREATE":
                      {
                        SystemOptions.GenSPCreate = true;
                        break;
                      }
                    case "DELETE":
                      {
                        SystemOptions.GenSPDelete = true;
                        break;
                      }
                    case "RETRIEVE":
                      {
                        SystemOptions.GenSPRetrieve = true;
                        break;
                      }
                    case "RETRIEVEALL":
                      {
                        SystemOptions.GenSPRetrieveAll = true;
                        break;
                      }
                    case "UPDATE":
                      {
                        SystemOptions.GenSPUpdate = true;
                        break;
                      }
                    case "AUDIT":
                      {
                       SystemOptions.Audit = true;
                        break;
                      }
                    case "DYNAMIC QUERIES":
                      {
                        SystemOptions.DynamicQueries = true;
                        break;
                      }
                    case "SEARCHABLE":
                      {
                        SystemOptions.Searchable = true;
                        break;
                      }
                    case "NOLIMIT":
                      {
                        SystemOptions.Limit = false;
                        SystemOptions.LimitTo = "";
                        break;
                      }
                    default :
                      {
                        SystemOptions.Limit = true;
                        SystemOptions.LimitTo = tableColumns[0][iDOOptionIdx];
                        break;
                      }
                  }
                }
                break;
              }
            case 1:
              {
                SystemOptions.CurrentEmbedObjects = tableColumns;
                break;
              }
            case 2:
              {
                SystemOptions.CurrentTableFields = tableColumns;
                for (int iColumnIdx = 0; iColumnIdx < tableColumns.Count; iColumnIdx++)
                {
                  if (tableColumns[0] != null)
                  {
                    string colname = tableColumns[iColumnIdx][0];
                    if (Convert.ToBoolean(tableColumns[iColumnIdx][4]))
                    {
                      m_PrimaryKeyColName = colname;
                      m_PrimaryKeyType = GetDBType(tableColumns[iColumnIdx][1], tableColumns[iColumnIdx][2]);
                    }

                    if (colname.ToUpper() == m_TableName.ToUpper() + "_ACTIVE")
                    {
                      m_ActiveColName = colname;
                    }
                  }
                }
                break;
              }
            case 3:
              {
                SystemOptions.CurrentSearchResults = tableColumns;
                break;
              }
          }
          iSettingsCounter++;
        }

        if (m_PrimaryKeyColName == "")
        {
          MessageBox.Show("Primary Key not specified on table " + m_TableName);
          return;
        }

        if (SystemOptions.GenStoredProcedures)
        {
          CreateStoredProcs();
        }
        else
        {
          tbMain.Enabled = true;
          tcStoredProcs.Enabled = false;
          tbMain.SelectTab(1);
          //bSubSettings.SelectedIndex = 0;
        }

        CreateDO(m_TableName);
        SystemOptions.GenDo = true;
        //if (chkAudit.Checked)
        //  CreateAuditCDO();
        CreateUnitTest();
        //tbMain.Enabled = true;
        //btnSave.Enabled = true;
        GrdTablesGenerated.Rows.Add(m_TableName, "Success", txtSPCreate.Text, "Success", txtSPDelete.Text, "Success", txtSPRetrieve.Text,
          "Success", txtSPRetrieveAll.Text, "Success", txtSPUpdate.Text, "Success", txtDO.Text, "Success", txtUnitTest.Text,"Success", txtDOPartial.Text);
      }
      GrdTablesGenerated.Rows[0].Selected = true;
      btnCreateFiles.Enabled = true;
    }

    private void btnReGenerate_Click(object sender, EventArgs e)
    {
      string generatedTableName = "";
      if (MessageBox.Show("Are you sure you want to re-generate all previously generated objects?", "Re-Generate", MessageBoxButtons.YesNo) == DialogResult.Yes)
      {
        frmRegenerate frmLoad = new frmRegenerate();
        frmLoad.RaptorUserName = thisSettings.UserName;

        if (RaptorSQLConn == null || RaptorSQLConn.State == ConnectionState.Closed)
        {
          SetRaptorDBConn();
        }

        frmLoad.RaptorSqlConn = RaptorSQLConn;
        frmLoad.ShowDialog();
        this.Refresh();
        if (frmLoad.ReasonSaved)
        {
          this.Cursor = Cursors.WaitCursor;
          #region Setup and regenerate
          DataSet DSPreviousGen = LoadPreviousGeneratedObjects();
          if (DSPreviousGen.Tables[0].Rows.Count > -1)
          {
            foreach (DataRow PrevDr in DSPreviousGen.Tables[0].Rows)
            {
              //DataSet DSGeneratedObject = LoadGenerateObject(row.Cells[1].Value.ToString());
              long generatedObjectID = 0;
              //foreach (DataRow dr in DSGeneratedObject.Tables[0].Rows)
              //{
              generatedObjectID = long.Parse(PrevDr["GenerateObject_ID"].ToString());
              generatedTableName = PrevDr["GenerateObject_Name"].ToString();
              //}
              DataSet DSDOOptions = LoadDOOptions(generatedObjectID);
              DataSet DSEmbedObjects = LoadEmbedObjects(generatedObjectID);
              DataSet DSSearchParameters = LoadSearchParameters(generatedObjectID);
              DataSet DSSearchResults = LoadSearchResults(generatedObjectID);

              List<List<String>> previouslyGeneratedDOOptions = new List<List<string>>();

              List<List<String>> previouslyGeneratedEmbedObjects = new List<List<string>>();

              List<List<String>> previouslyGeneratedSearchParams = new List<List<string>>();

              List<List<String>> previouslyGeneratedSearchResults = new List<List<string>>();

              List<List<List<string>>> generateSettings = new List<List<List<string>>>();

              if (DSDOOptions.Tables[0].Rows.Count > 0)
              {
                foreach (DataRow dr in DSDOOptions.Tables[0].Rows)
                {
                  List<String> DOOptions = new List<string>();
                  if (bool.Parse(dr["DOOption_Create"].ToString())) { DOOptions.Add("CREATE"); }
                  if (bool.Parse(dr["DOOption_Delete"].ToString())) { DOOptions.Add("DELETE"); }
                  if (bool.Parse(dr["DOOption_Retrieve"].ToString())) { DOOptions.Add("RETRIEVE"); }
                  if (bool.Parse(dr["DOOption_RetrieveAll"].ToString())) { DOOptions.Add("RETRIEVEALL"); }
                  if (bool.Parse(dr["DOOption_Update"].ToString())) { DOOptions.Add("UPDATE"); }
                  if (bool.Parse(dr["DOOption_NoLimit"].ToString())) { DOOptions.Add("NOLIMIT"); }
                  if (bool.Parse(dr["DOOption_Audit"].ToString())) { DOOptions.Add("AUDIT"); }
                  if (bool.Parse(dr["DOOption_Searchable"].ToString())) { DOOptions.Add("SEARCHABLE"); }
                  if (bool.Parse(dr["DOOption_StandardLookup"].ToString())) { DOOptions.Add("STANDARD"); }
                  if (bool.Parse(dr["DOOption_StandardLookupDescription"].ToString())) { DOOptions.Add("STANDARDDESCRIPTION"); }

                  previouslyGeneratedDOOptions.Add(DOOptions);
                }
                generateSettings.Add(previouslyGeneratedDOOptions);
              }
              else
              {
                generateSettings.Add(previouslyGeneratedDOOptions);
              }

              if (DSEmbedObjects.Tables[0].Rows.Count > 0)
              {
                foreach (DataRow dr in DSEmbedObjects.Tables[0].Rows)
                {
                  List<String> EmbedSettings = new List<string>();
                  EmbedSettings.Add(dr["EmbedObject_ReferencedTableName"].ToString());
                  EmbedSettings.Add(dr["EmbedObject_ReferencedTableLinkField"].ToString());
                  EmbedSettings.Add(dr["EmbedObject_EmbedTableName"].ToString());
                  EmbedSettings.Add(dr["EmbedObject_EmbedTableLinkField"].ToString());
                  EmbedSettings.Add(dr["EmbedObject_IsObject"].ToString());
                  EmbedSettings.Add(dr["EmbedObject_IsList"].ToString());
                  EmbedSettings.Add(dr["EmbedObject_IsActive"].ToString());
                  previouslyGeneratedEmbedObjects.Add(EmbedSettings);
                }
                generateSettings.Add(previouslyGeneratedEmbedObjects);
              }
              else
              {
                generateSettings.Add(previouslyGeneratedEmbedObjects);
              }

              if (DSSearchParameters.Tables[0].Rows.Count > 0)
              {
                foreach (DataRow dr in DSSearchParameters.Tables[0].Rows)
                {
                  List<String> SearchParameters = new List<string>();
                  SearchParameters.Add(dr["SearchParameter_FieldName"].ToString());
                  SearchParameters.Add(dr["SearchParameter_DataType"].ToString());
                  SearchParameters.Add(dr["SearchParameter_FieldLength"].ToString());
                  SearchParameters.Add(dr["SearchParameter_IsNullable"].ToString());
                  SearchParameters.Add(dr["SearchParameter_IsPrimaryKey"].ToString());
                  SearchParameters.Add(dr["SearchParameter_IsSearchParameter"].ToString());
                  SearchParameters.Add(dr["SearchParameter_OrdinalPosition"].ToString());
                  SearchParameters.Add(dr["SearchParameter_IsOptional"].ToString());
                  SearchParameters.Add(dr["SearchParameter_IsRetrieveAll"].ToString());
                  previouslyGeneratedSearchParams.Add(SearchParameters);
                }
                generateSettings.Add(previouslyGeneratedSearchParams);
              }
              else
              {
                generateSettings.Add(previouslyGeneratedSearchParams);
              }

              if (DSSearchResults.Tables[0].Rows.Count > 0)
              {
                foreach (DataRow dr in DSSearchResults.Tables[0].Rows)
                {
                  List<String> SearchResults = new List<string>();
                  SearchResults.Add(dr["SearchResult_FieldName"].ToString());
                  previouslyGeneratedSearchResults.Add(SearchResults);
                }
                generateSettings.Add(previouslyGeneratedSearchResults);
              }
              else
              {
                generateSettings.Add(previouslyGeneratedSearchResults);
              }
              PreviouslyGeneratedSettings.Add(generatedTableName, generateSettings);
            }
          }
          this.SelectionSetting = PreviouslyGeneratedSettings;
          m_PromptOverwrite = false;
          btnGenerate_Click(sender, e);
          btnCreateFiles_Click(sender, e);
          m_PromptOverwrite = true;
          #endregion
          this.Cursor = Cursors.Default;
        }
        else
        {
          MessageBox.Show("You have chosen not to re-generate all the previously generated objects, No Generation has occurred");
        }
      }

      //for (int iGenIdx = 0; iGenIdx < PreviouslyGeneratedSettings.Count; iGenIdx++)
      //{
      //  //Start The Gen Procedure here
      //}
    }

    private void btnSetDOPath_Click(object sender, EventArgs e)
    {
      DlgFolderBrowser.SelectedPath = txtDOPath.Text;
      if (DlgFolderBrowser.ShowDialog() == DialogResult.OK)
        txtDOPath.Text = DlgFolderBrowser.SelectedPath;
    }

    private void btnSetUnitTestPath_Click(object sender, EventArgs e)
    {
      DlgFolderBrowser.SelectedPath = txtUnitTestPath.Text;
      if (DlgFolderBrowser.ShowDialog() == DialogResult.OK)
        txtUnitTestPath.Text = DlgFolderBrowser.SelectedPath;
    }

    private void btnExit_Click(object sender, EventArgs e)
    {
      this.Close();
      this.Dispose();
      Application.Exit();
    }

    private void btnSPBrowse_Click(object sender, EventArgs e)
    {
      DlgFolderBrowser.SelectedPath = txtStoredProcSavePath.Text;
      if (DlgFolderBrowser.ShowDialog() == DialogResult.OK)
        txtStoredProcSavePath.Text = DlgFolderBrowser.SelectedPath;
    }

    private void btnSaveFileSettings_Click(object sender, EventArgs e)
    {
      SaveFileSettings();

      MessageBox.Show("File Save Settings Successfully Saved!");
    }

    private void btnSaveDBSettings_Click(object sender, EventArgs e)
    {
      SaveDBSettings();

      MessageBox.Show("Prms Database Settings Successfully Saved!");
    }

    private void btnSaveRaptorDBSettings_Click(object sender, EventArgs e)
    {
      SaveRaptorDBSettings();

      MessageBox.Show("Raptor Database Settings Successfully Saved!");
    }

    private void btnSaveNameSpaceSettings_Click(object sender, EventArgs e)
    {
      SaveNamespaceSettings();

      MessageBox.Show("Namespace Settings Successfully Saved!");
    }

    private void btnInstructions_Click(object sender, EventArgs e)
    {
      frmInstructions frmShow = new frmInstructions();
      frmShow.ShowDialog();
    }

    private void GrdGenerate_CellDirtyStateChanged(object sender, EventArgs e)
    {
      GrdGenerate.CommitEdit(DataGridViewDataErrorContexts.CurrentCellChange);
    }

    private void GrdGenerate_CellValueChanged(object sender, DataGridViewCellEventArgs e)
    {
      if (e.RowIndex >= 0)
      {
        string generatedTableName = "";
        switch (e.ColumnIndex)
        {
          case 0:
            {
              if (bool.Parse((GrdGenerate.Rows[e.RowIndex].Cells[5] as DataGridViewCheckBoxCell).Value.ToString()))
              {
                (GrdGenerate.Rows[e.RowIndex].Cells[5] as DataGridViewCheckBoxCell).Value = false;
                DataSet DSGeneratedObject = LoadGenerateObject(GrdGenerate.Rows[e.RowIndex].Cells[1].Value.ToString());
                long generatedObjectID = 0;
                foreach(DataRow dr in DSGeneratedObject.Tables[0].Rows)
                {
                  generatedObjectID = long.Parse(dr["GenerateObject_ID"].ToString());
                  generatedTableName = dr["GenerateObject_Name"].ToString();
                }
                DataSet DSDOOptions = LoadDOOptions(generatedObjectID);
                DataSet DSEmbedObjects = LoadEmbedObjects(generatedObjectID);
                DataSet DSSearchParameters = LoadSearchParameters(generatedObjectID);
                DataSet DSSearchResults = LoadSearchResults(generatedObjectID);

                List<List<String>> previouslyGeneratedDOOptions = new List<List<string>>();

                List<List<String>> previouslyGeneratedEmbedObjects = new List<List<string>>();

                List<List<String>> previouslyGeneratedSearchParams = new List<List<string>>();

                List<List<String>> previouslyGeneratedSearchResults = new List<List<string>>();

                List<List<List<string>>> generateSettings = new List<List<List<string>>>();
                
                if (DSDOOptions.Tables[0].Rows.Count > 0)
                {
                  foreach (DataRow dr in DSDOOptions.Tables[0].Rows)
                  {
                    List<String> DOOptions = new List<string>();
                    if (bool.Parse(dr["DOOption_Create"].ToString())) { DOOptions.Add("CREATE"); }
                    if (bool.Parse(dr["DOOption_Delete"].ToString())) { DOOptions.Add("DELETE"); }
                    if (bool.Parse(dr["DOOption_Retrieve"].ToString())) { DOOptions.Add("RETRIEVE"); }
                    if (bool.Parse(dr["DOOption_RetrieveAll"].ToString())) { DOOptions.Add("RETRIEVEALL"); }
                    if (bool.Parse(dr["DOOption_Update"].ToString())) { DOOptions.Add("UPDATE"); }
                    if (bool.Parse(dr["DOOption_NoLimit"].ToString())) { DOOptions.Add("NOLIMIT"); }
                    if (bool.Parse(dr["DOOption_Audit"].ToString())) { DOOptions.Add("AUDIT"); }
                    if (bool.Parse(dr["DOOption_Searchable"].ToString())) { DOOptions.Add("SEARCHABLE"); }
                    if (bool.Parse(dr["DOOption_StandardLookup"].ToString())) { DOOptions.Add("STANDARD"); }
                    if (bool.Parse(dr["DOOption_StandardLookupDescription"].ToString())) { DOOptions.Add("STANDARDDESCRIPTION"); }

                    previouslyGeneratedDOOptions.Add(DOOptions);
                  }
                  generateSettings.Add(previouslyGeneratedDOOptions);
                }
                else
                {
                  generateSettings.Add(previouslyGeneratedDOOptions);
                }

                if (DSEmbedObjects.Tables[0].Rows.Count > 0)
                {
                  foreach (DataRow dr in DSEmbedObjects.Tables[0].Rows)
                  {
                    List<String> EmbedSettings = new List<string>();
                    EmbedSettings.Add(dr["EmbedObject_ReferencedTableName"].ToString());
                    EmbedSettings.Add(dr["EmbedObject_ReferencedTableLinkField"].ToString());
                    EmbedSettings.Add(dr["EmbedObject_EmbedTableName"].ToString());
                    EmbedSettings.Add(dr["EmbedObject_EmbedTableLinkField"].ToString());
                    EmbedSettings.Add(dr["EmbedObject_IsObject"].ToString());
                    EmbedSettings.Add(dr["EmbedObject_IsList"].ToString());
                    EmbedSettings.Add(dr["EmbedObject_IsActive"].ToString());
                    previouslyGeneratedEmbedObjects.Add(EmbedSettings);
                  }
                  generateSettings.Add(previouslyGeneratedEmbedObjects);
                }
                else
                {
                  generateSettings.Add(previouslyGeneratedEmbedObjects);
                }

                if (DSSearchParameters.Tables[0].Rows.Count > 0)
                {
                  foreach (DataRow dr in DSSearchParameters.Tables[0].Rows)
                  {
                    List<String> SearchParameters = new List<string>();
                    SearchParameters.Add(dr["SearchParameter_FieldName"].ToString());
                    SearchParameters.Add(dr["SearchParameter_DataType"].ToString());
                    SearchParameters.Add(dr["SearchParameter_FieldLength"].ToString());
                    SearchParameters.Add(dr["SearchParameter_IsNullable"].ToString());
                    SearchParameters.Add(dr["SearchParameter_IsPrimaryKey"].ToString());
                    SearchParameters.Add(dr["SearchParameter_IsSearchParameter"].ToString());
                    SearchParameters.Add(dr["SearchParameter_OrdinalPosition"].ToString());
                    SearchParameters.Add(dr["SearchParameter_IsOptional"].ToString());
                    SearchParameters.Add(dr["SearchParameter_IsRetrieveAll"].ToString());
                    SearchParameters.Add(dr["SearchParameter_IsDependant"].ToString());
                    previouslyGeneratedSearchParams.Add(SearchParameters);
                  }
                  generateSettings.Add(previouslyGeneratedSearchParams);
                }
                else
                {
                  generateSettings.Add(previouslyGeneratedSearchParams);
                }

                if (DSSearchResults.Tables[0].Rows.Count > 0)
                {
                  foreach (DataRow dr in DSSearchResults.Tables[0].Rows)
                  {
                    List<String> SearchResults = new List<string>();
                    SearchResults.Add(dr["SearchResult_FieldName"].ToString());
                    previouslyGeneratedSearchResults.Add(SearchResults);
                  }
                  generateSettings.Add(previouslyGeneratedSearchResults);
                }
                else
                {
                  generateSettings.Add(previouslyGeneratedSearchResults);
                }

                PreviouslyGeneratedSettings.Add(generatedTableName, generateSettings);
              }
              if (bool.Parse((GrdGenerate.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value.ToString()))
              {
                frmSearchColumns loadForm = new frmSearchColumns();
                loadForm.TableColumns = GrdGenerate.Rows[e.RowIndex].Tag as DataTable;
                loadForm.returnForm = this;
                loadForm.TableName = GrdGenerate.Rows[e.RowIndex].Cells[1].Value.ToString();
                if (this.StoredProcName != null)
                  loadForm.StoredProcName = this.StoredProcName;

                loadForm.ActiveConn = PRMSSQLConn;
                if (generatedTableName.Length > 0)
                {
                  if (PreviouslyGeneratedSettings[generatedTableName][2] != null && PreviouslyGeneratedSettings[generatedTableName][2].Count > 0)
                    loadForm.SelectedList = PreviouslyGeneratedSettings[generatedTableName][2];
                  if (PreviouslyGeneratedSettings[generatedTableName].Count > 2)
                  {
                    if (PreviouslyGeneratedSettings[generatedTableName][0] != null && PreviouslyGeneratedSettings[generatedTableName][0].Count > 0)
                      loadForm.SelectedDOOptions = PreviouslyGeneratedSettings[generatedTableName][0];
                    if (PreviouslyGeneratedSettings[generatedTableName].Count > 3)
                    {
                      if (PreviouslyGeneratedSettings[generatedTableName][3] != null && PreviouslyGeneratedSettings[generatedTableName][3].Count > 0)
                        loadForm.selectedSearchResults = PreviouslyGeneratedSettings[generatedTableName][3];
                    }
                  }
                }
                loadForm.ShowDialog();
                if (this.m_DOOptions == null)
                  MessageBox.Show("Error Setting DO Options");

                if (this.m_SelectedTableColumns != null)
                {
                  if (this.m_SelectedTableColumns.Count <= 0)
                  {
                    (GrdGenerate.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value = false;
                  }
                  else
                  {
                    (GrdGenerate.Rows[e.RowIndex].Cells[2] as DataGridViewComboBoxCell).Items.Clear();
                    for (int i = 0; i < this.m_SelectedTableColumns.Count; i++)
                    {
                      (GrdGenerate.Rows[e.RowIndex].Cells[2] as DataGridViewComboBoxCell).Items.Add(this.m_SelectedTableColumns[i][0]);
                    }
                    DataGridViewComboBoxCell column = (GrdGenerate.Rows[e.RowIndex].Cells[2] as DataGridViewComboBoxCell);
                    (GrdGenerate.Rows[e.RowIndex].Cells[3] as DataGridViewCheckBoxCell).Value = true;
                  }
                }
                else
                {
                  (GrdGenerate.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value = false;
                }
              }
              break;
            }
          case 3:
            {
              ////ConstraintCollection constraint = sourceTables.Constraints;
              //DataTable dt = PRMSSQLConn.GetSchema("Foreign_Keys");
              bool bNoEmbeds = true;

              if (bool.Parse((GrdGenerate.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value.ToString()))
              {
                if (!bool.Parse((GrdGenerate.Rows[e.RowIndex].Cells[0] as DataGridViewCheckBoxCell).Value.ToString()))
                {
                  MessageBox.Show("You cannot specify embedded objects when the object has not been marked for generation!");
                }
                else
                {
                  frmEmbededObjects loadForm = new frmEmbededObjects();
                  //loadForm.TableColumns = GrdGenerate.Rows[e.RowIndex].Tag as DataTable;
                  loadForm.TableName = GrdGenerate.Rows[e.RowIndex].Cells[1].Value.ToString();
                  loadForm.returnForm = this;
                  string generatedEmbedTableName = loadForm.TableName;
                  if (generatedEmbedTableName.Length > 0 && PreviouslyGeneratedSettings.Count > 0)
                  {
                    if (CheckForGeneratedObject(generatedEmbedTableName))
                    {
                      if (PreviouslyGeneratedSettings[generatedEmbedTableName][1] != null && PreviouslyGeneratedSettings[generatedEmbedTableName][1].Count > 0)
                        loadForm.SelectedList = PreviouslyGeneratedSettings[generatedEmbedTableName][1];
                    }
                  }
                  loadForm.ActiveConn = PRMSSQLConn;
                  loadForm.ShowDialog();

                  if (this.m_EmbededObjectList != null && this.m_EmbededObjectList.Count > 0)
                  {
                    foreach (List<String> embeddedObject in this.EmbededObjectList)
                    {
                      if (bool.Parse(embeddedObject[4].ToString()) || bool.Parse(embeddedObject[5].ToString()))
                      {
                        bNoEmbeds = false;
                        break;
                      }
                    }
                    //(GrdGenerate.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value = false;
                  }
                  if (!bNoEmbeds)
                  {
                    (GrdGenerate.Rows[e.RowIndex].Cells[4] as DataGridViewComboBoxCell).Items.Clear();

                    //(GrdGenerate.Rows[e.RowIndex].Cells[4] as DataGridViewComboBoxCell).Items.Add("[Click for List]");
                    for (int i = 0; i < this.m_EmbededObjectList.Count; i++)
                    {
                      (GrdGenerate.Rows[e.RowIndex].Cells[4] as DataGridViewComboBoxCell).Items.Add(this.m_EmbededObjectList[i][2]);
                    }
                    (GrdGenerate.Rows[e.RowIndex].Cells[4] as DataGridViewComboBoxCell).Selected = true;
                  }
                  else
                  {
                    bNoEmbeds = true;
                    this.EmbededObjectList = null;
                    (GrdGenerate.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value = false;
                  }
                  //Add to dictionary here
                  List<List<List<String>>> SettingsList = new List<List<List<string>>>();
                  SettingsList.Add(this.m_DOOptions);
                  SettingsList.Add(this.m_EmbededObjectList);
                  SettingsList.Add(this.m_SelectedTableColumns);
                  SettingsList.Add(this.m_SelectedSearchResults);
                  this.m_DOOptions = null;
                  this.m_EmbededObjectList = null;
                  this.m_SelectedTableColumns = null;
                  this.m_SelectedSearchResults = null;
                  foreach (KeyValuePair<string, List<List<List<string>>>> table in this.SelectionSetting)
                  {
                    if (table.Key == GrdGenerate.Rows[e.RowIndex].Cells[1].Value.ToString())
                    {
                      SelectionSetting.Remove(table.Key);
                      break;
                    }
                  }
                  SelectionSetting.Add(GrdGenerate.Rows[e.RowIndex].Cells[1].Value.ToString(), SettingsList);
                }
              }
              break;
            }
          case 4:
            {
              break;
            }
        }
      }
    }

    private void btnPreview_Click(object sender, EventArgs e)
    {
      frmPreview loadForm = new frmPreview();
      loadForm.SelectionObjects = SelectionSetting;
      loadForm.returnForm = this;
      loadForm.ShowDialog();
    }

    private void GrdTablesGenerated_SelectionChanged(object sender, EventArgs e)
    {
      if (GrdTablesGenerated.SelectedRows.Count > 0)
      {
        txtSPCreate.Text = GrdTablesGenerated.SelectedRows[0].Cells[2].Value.ToString();
        txtSPDelete.Text = GrdTablesGenerated.SelectedRows[0].Cells[4].Value.ToString();
        txtSPRetrieve.Text = GrdTablesGenerated.SelectedRows[0].Cells[6].Value.ToString();
        txtSPRetrieveAll.Text = GrdTablesGenerated.SelectedRows[0].Cells[8].Value.ToString();
        txtSPUpdate.Text = GrdTablesGenerated.SelectedRows[0].Cells[10].Value.ToString();
        txtDO.Text = GrdTablesGenerated.SelectedRows[0].Cells[12].Value.ToString();
        txtUnitTest.Text = GrdTablesGenerated.SelectedRows[0].Cells[14].Value.ToString();
        txtDOPartial.Text = GrdTablesGenerated.SelectedRows[0].Cells[16].Value.ToString();
      }
      else
      {
        ClearTextFields();
      }
    }

    private void btnExit_Click_1(object sender, EventArgs e)
    {
      this.Close();
    }

    private void btnCreateFiles_Click(object sender, EventArgs e)
    {
			int generatedFilesCounter = 0;
			int generatedTablesCounter = 0;
      int iCellIdx = 0;
      string[] sSPs = new string[11];

      sSPs[0] = "";
      sSPs[1] = "";
      sSPs[2] = "_Create.sql";
      sSPs[3] = "";
      sSPs[4] = "_Delete.sql";
      sSPs[5] = "";
      sSPs[6] = "_Retrieve.sql";
      sSPs[7] = "";
      sSPs[8] = "_RetrieveAll.sql";
      sSPs[9] = "";
      sSPs[10] = "_Update.sql";

      foreach (DataGridViewRow thisRow in GrdTablesGenerated.Rows)
      {
        foreach (DataGridViewCell thisCell in thisRow.Cells)
        {
          switch (iCellIdx)
          {
            case 2:
            case 4:
            case 6:
            case 8:
            case 10:
              {
								generatedFilesCounter++;
                string storedProcedureFolder = thisRow.Cells[0].Value.ToString();
                SaveFiles("SP" + thisRow.Cells[0].Value.ToString() + sSPs[iCellIdx], txtStoredProcSavePath.Text + "\\" + storedProcedureFolder, thisCell.Value.ToString());
                break;
              }
            case 12:
              {
								generatedFilesCounter++;
                SaveFiles("DO" + thisRow.Cells[0].Value.ToString() + ".cs", txtDOPath.Text, thisCell.Value.ToString());
                break;
              }
            case 14:
              {
								generatedFilesCounter++;
                SaveFiles("UT" + thisRow.Cells[0].Value.ToString() + ".cs", txtUnitTestPath.Text, thisCell.Value.ToString());
                break;
              }
            case 16:
              {
                if (thisCell.Value.ToString().Length > 0)
                {
                  generatedFilesCounter++;
                  SaveFiles("DO" + thisRow.Cells[0].Value.ToString() + txtPartialClassExtension.Text + ".cs", txtDOPath.Text, thisCell.Value.ToString());
                }
                break;
              }
          }
          iCellIdx++;
        }
				generatedTablesCounter++;
        iCellIdx = 0;
        //SaveRaptorDBSettings();
        //SaveRaptorObjectsSettings();
      }
      if (!m_PromptOverwrite)
        m_PromptOverwrite = true;

      MessageBox.Show(this, string.Format("{0} Table(s) Processed.\n{1} Files successfully saved, All generated objects settings will now be reset!", generatedTablesCounter, generatedFilesCounter), "Results", MessageBoxButtons.OK, MessageBoxIcon.Information);

      reloadingForm = true;
      this.Close();

      frmMain mainForm = new frmMain();
      mainForm.Show();

      Application.DoEvents();
      mainForm.btnLoadDataStructureClick(this, null);
    }
    #endregion

    #region Class Methods

    #region Save File Methods
    private void SaveFiles(string fileName, string fileFolder, string SaveText)
    {
      FileStream fileStream;

      // Make sure the directory exists
      if (!Directory.Exists(fileFolder)) { Directory.CreateDirectory(fileFolder); }

      if (File.Exists(fileFolder + "/" + fileName))
      {
        if (m_PromptOverwrite)
        {
          DialogResult promptDialogResult = new DialogResult();
          promptDialogResult = MessageBox.Show("The " + fileName + " already exists, do you wish to overwrite all files? Select No to only replace this file, or cancel to not overwrite this file.", "File exists", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);

          if (promptDialogResult == DialogResult.Yes)
            m_PromptOverwrite = false;
          else if (promptDialogResult == DialogResult.No)
            m_PromptOverwrite = true;
          else if(promptDialogResult == DialogResult.Cancel)
            return;
          //if (MessageBox.Show("The " + fileName + " already exists, do you wish to overwrite all files? Select No to only replace this file, or cancel to not overwrite this file.", "File exists", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question) == DialogResult.Yes)
          //  m_PromptOverwrite = false;
          //else if(
          //  return;
        }
      }

      // Delete the file if it exists

      if (File.Exists(fileFolder + "/" + fileName)) { File.Delete(fileFolder + "/" + fileName); }

      // Create the required objects
      fileStream = new FileStream(fileFolder + "/" + fileName, FileMode.CreateNew);
      // Saves the Text via a fileStream created by the OpenFile method.
      StreamWriter sw = new StreamWriter(fileStream);

      sw.Write(SaveText);
      sw.Close();
      fileStream.Close();
    }
    #endregion

    #region Settings
    private void LoadSettings()
    {
      txtDOPath.Text = thisSettings.DOPath;
      txtUnitTestPath.Text = thisSettings.UnitTestPath;
      txtStoredProcSavePath.Text = thisSettings.SQLPath;
      txtDBName.Text = thisSettings.DBName;
      txtDBServer.Text = thisSettings.DBServer;
      txtDBUserID.Text = thisSettings.DBUserID;
      txtDBPassword.Text = thisSettings.DBPassword;
			chkWindowsAuth.Checked = thisSettings.UseWindowsAuth;
      txtRaptorDBName.Text = thisSettings.RaptorDBName;
      txtRaptorDBServer.Text = thisSettings.RaptorDBServer;
      txtRaptorDBUserID.Text = thisSettings.RaptorDBUserID;
      txtRaptorDBPassword.Text = thisSettings.RaptorDBPassword;
      chkRaptorUseWindowsAuth.Checked = thisSettings.RaptorUseWindowsAuth;
      txtNSDO.Text = thisSettings.NamespaceDO;
      txtNSUT.Text = thisSettings.NamespaceUT;
      txtUserName.Text = thisSettings.UserName;
      txtPartialClassExtension.Text = thisSettings.PartialClassExtension;
    }

    private int CheckSettings()
    {
      int iResult = 0;

      if (txtDOPath.Text.Length == 0 || txtUserName.Text.Length == 0)
        iResult = 1;

      if (txtDBName.Text.Length == 0 || txtDBServer.Text.Length == 0)
        iResult = 2;
      else
        if (!chkWindowsAuth.Checked && txtDBUserID.Text.Length == 0)
          iResult = 2;

      if (txtRaptorDBName.Text.Length == 0 || txtRaptorDBServer.Text.Length == 0)
        iResult = 2;
      else
        if (!chkRaptorUseWindowsAuth.Checked && txtRaptorDBUserID.Text.Length == 0)
          iResult = 2;

      if (txtNSDO.Text.Length == 0)
        iResult = 3;

      return iResult;
    }

    private void SaveFileSettings()
    {
      thisSettings.DOPath = txtDOPath.Text;
      thisSettings.UnitTestPath = txtUnitTestPath.Text;
      thisSettings.SQLPath = txtStoredProcSavePath.Text;
      thisSettings.UserName = txtUserName.Text;
      thisSettings.PartialClassExtension = txtPartialClassExtension.Text;

      thisSettings.Save();
    }

    private void SaveDBSettings()
    {
      thisSettings.DBName = txtDBName.Text;
      thisSettings.DBServer = txtDBServer.Text;
      thisSettings.DBUserID = txtDBUserID.Text;
      thisSettings.DBPassword = txtDBPassword.Text;
      thisSettings.UseWindowsAuth = chkWindowsAuth.Checked;

      thisSettings.Save();
    }

    private void SaveRaptorDBSettings()
    {
      thisSettings.RaptorDBName = txtRaptorDBName.Text;
      thisSettings.RaptorDBServer = txtRaptorDBServer.Text;
      thisSettings.RaptorDBUserID = txtRaptorDBUserID.Text;
      thisSettings.RaptorDBPassword = txtRaptorDBPassword.Text;
      thisSettings.RaptorUseWindowsAuth = chkRaptorUseWindowsAuth.Checked;

      thisSettings.Save();
    }

    private void SaveNamespaceSettings()
    {
      thisSettings.NamespaceDO = txtNSDO.Text;
      thisSettings.NamespaceUT = txtNSUT.Text;
      thisSettings.Save();
    }
    #endregion

    #region Clear Settings Methods
    private void ClearRaptorSettings()
    {
      this.Close();
      this.Show();
    }
    #endregion

    #region DB Methods

    #region PRMS DB COnnection
    private void SetPrmsDBConn()
    {
      string sConnectionString = "Data Source=" + txtDBServer.Text.Trim() + ";Initial Catalog=" + txtDBName.Text + ";";
      if (chkWindowsAuth.Checked)
        sConnectionString += "Integrated Security=SSPI;";
      else
        sConnectionString += "User ID=" + txtDBUserID.Text.Trim() + ";Password=" + txtDBPassword.Text.Trim() + ";";

      PRMSSQLConn = new SqlConnection(sConnectionString);
      PRMSSQLConn.Open();
    }
    #endregion

    #region Raptor DB COnnection
    private void SetRaptorDBConn()
    {
      string sConnectionString = "Data Source=" + txtRaptorDBServer.Text.Trim() + ";Initial Catalog=" + txtRaptorDBName.Text + ";";
      if (chkRaptorUseWindowsAuth.Checked)
        sConnectionString += "Integrated Security=SSPI;";
      else
        sConnectionString += "User ID=" + txtRaptorDBUserID.Text.Trim() + ";Password=" + txtRaptorDBPassword.Text.Trim() + ";";

      RaptorSQLConn = new SqlConnection(sConnectionString);
      RaptorSQLConn.Open();
    }
    #endregion

    #region DataType Stuff

    private string ForceDataType(string DataType)
    {
      string sReturn = DataType;
      switch (DataType)
      {
        case "System.Int16":
          {
            sReturn = "System.Int32";
            break;
          }
        case "System.Int64":
          {
            sReturn = "System.Int32";
            break;
          }
      }
      return (sReturn);
    }

    private string GetDataType(string DataType)
    {
      string sReturn = "";
      switch (DataType)
      {
        case "System.Int16":
          {
            sReturn = "int";
            break;
          }
        case "System.Int32":
          {
            sReturn = "long";
            break;
          }
        case "System.Int64":
          {
            sReturn = "long";
            break;
          }
        case "System.String":
          {
            sReturn = "string";
            break;
          }
        case "System.Boolean":
          {
            sReturn = "bool";
            break;
          }
        case "System.Byte[]":	// System.TimeStamp does not exist - returned as a Byte[]... Trevisen
          {
            sReturn = "DateTime";
            break;
          }
        case "System.DateTime":
          {
            sReturn = "DateTime";
            break;
          }
        case "System.Decimal":
          {
            sReturn = "decimal";
            break;
          }
        case "System.Double":
          {
            sReturn = "decimal";
            break;
          }
        case "System.Xml":
          {
            sReturn = "xml";
            break;
          }
        default:
          {
            throw new Exception("GetDataType: Unhandled Data Type " + DataType);
          }
      }
      return sReturn;
    }

    private string GetSystemDataType(string DataType)
    {
      string sReturn = "";
      switch (DataType)
      {
				case "decimal":
					{
						sReturn = "DbType.Decimal";
						break;
					}
				case "image":
        case "uniqueidentifier":
					{
						sReturn = "DbType.Object"; // this should be something like SqlDbType.Image
						break;
					}
				case "smallint":
        case "System.Int16":
          {
            sReturn = "DbType.Int16";
            break;
          }
				case "money":
					{
						sReturn = "DbType.Currency";
						break;
					}
        case "int":
          {
            sReturn = "DbType.Int32";
            break;
          }
        case "bigint":
          {
            sReturn = "DbType.Int64";
            break;
          }
        case "numeric":
          {
            sReturn = "DbType.Int64";
            break;
          }
        case "varchar":
          {
            sReturn = "DbType.String";
            break;
          }
        case "char":
          {
            sReturn = "DbType.String";
            break;
          }
        case "text":
          {
            sReturn = "DbType.String";
            break;
          }
        case "nvarchar":
          {
            sReturn = "DbType.String";
            break;
          }
        case "bit":
          {
            sReturn = "DbType.Boolean";
            break;
          }
        case "System.Byte[]":	// System.TimeStamp does not exist - returned as a Byte[]... Trevisen
          {
            sReturn = "DbType.DateTime";
            break;
          }
        case "datetime":
          {
            sReturn = "DbType.DateTime";
            break;
          }
        case "float":
          {
            sReturn = "DbType.Decimal";
            break;
          }
        case "System.Double":
          {
            sReturn = "DbType.Decimal";
            break;
          }
        case "System.Xml":
          {
            sReturn = "DbType.Xml";
            break;
          }
        default:
          {
            throw new Exception("GetDataType: Unhandled Data Type " + DataType);
          }
      }
      return sReturn;
    }

    private string GetDataTypeConverter(string DataType)
    {
      string sReturn = "";
      switch (DataType)
      {
        case "System.Int16":
          {
            sReturn = "Convert.ToInt32";
            break;
          }
        case "System.Int32":
          {
            sReturn = "Convert.ToInt32";
            break;
          }
        case "System.Int64":
          {
            sReturn = "Convert.ToInt32";
            break;
          }
        case "System.String":
          {
            sReturn = "";
            break;
          }
        case "System.Boolean":
          {
            sReturn = "Convert.ToBoolean";
            break;
          }
        case "System.Byte[]":	// System.TimeStamp does not exist - returned as a Byte[]
          {
            sReturn = "Convert.ToDateTime";
            break;
          }
        case "System.DateTime":
          {
            sReturn = "Convert.ToDateTime";
            break;
          }
        case "System.Decimal":
          {
            sReturn = "Convert.ToDecimal";
            break;
          }
        case "System.Double":
          {
            sReturn = "Convert.ToDecimal";
            break;
          }
        case "System.Xml":
          {
            sReturn = "";
            break;
          }
        default:
          {
            throw new Exception("GetDataType: Unhandled Data Type " + DataType);
          }
      }
      return sReturn;
    }

    private string GetAppDataType(string DataType, string length)
    {
      string sReturn = "";
      switch (DataType)
      {
				case "decimal":
					{
						sReturn = "decimal";
						break;
					}
				case "image":
					{
						sReturn = "byte[]";
						break;
					}
				case "money":
					{
						sReturn = "decimal";
						break;
					}
        case "bigint":
          {
            sReturn = "long";
            break;
          }
				case "smallint":
					{
						sReturn = "short";
						break;
					}
        case "numeric":
          {
            sReturn = "long";
            break;
          }
        case "int":
          {
            sReturn = "int";
            break;
          }
        case "System.Int64":
          {
            sReturn = "bigint";
            break;
          }
        case "varchar":
        case "uniqueidentifier":
          {
            sReturn = "string";
            break;
          }
        case "char":
          {
            sReturn = "string";
            break;
          }
        case "text":
          {
            sReturn = "string";
            break;
          }
        case "nvarchar":
          {
            sReturn = "string";
            break;
          }
        case "bit":
          {
            sReturn = "bool";
            break;
          }
        case "datetime":	// System.TimeStamp does not exist - returned as a Byte[]
          {
            sReturn = "DateTime";
            break;
          }
        case "System.DateTime":
          {
            sReturn = "DateTime";
            break;
          }
        case "float":
          {
            sReturn = "decimal";
            break;
          }
        case "System.Double":
          {
            sReturn = "decimal";
            break;
          }
        case "System.Xml":
          {
            sReturn = "xml";
            break;
          }
        default:
          {
            throw new Exception("Unhandled GetDBType Type: " + DataType);
          }
      }
      return sReturn;
    }

    private string GetDBType(string dataType, string dataLength)
    {
        string sReturn = "";
        switch (dataType)
        {
					case "decimal":
						{
							return "decimal";
							break;
						}
					case "image":
						{
							sReturn = "image";
							break;
						}
					case "money":
						{
							sReturn = "money";
							break;
						}
					case "smallint":
						{
							sReturn = "smallint";
							break;
						}
          case "int":
            {
              sReturn = "int";
              break;
            }
          case "bigint":
            {
              sReturn = "bigint";
              break;
            }
          case "numeric":
            {
              sReturn = "numeric(9,0)";// +dataLength + ")";
              break;
            }
          case "varchar":
            {
              sReturn = "varchar(" + dataLength + ")";
              break;
            }
          case "char":
            {
              sReturn = "char(" + dataLength + ")";
              break;
            }
          case "text":
            {
              sReturn = "text";
              break;
            }
          case "nvarchar":
            {
              sReturn = "nvarchar";
              break;
            }
          case "bit":
            {
              sReturn = "bit";
              break;
            }
          case "System.Byte[]":	// System.TimeStamp does not exist - returned as a Byte[]
            {
              sReturn = "DateTime";
              break;
            }
          case "datetime":
            {
              sReturn = "DateTime";
              break;
            }
          case "float":
            {
              sReturn = "decimal";
              break;
            }
          case "uniqueidentifier":
            {
              sReturn = "uniqueidentifier";
              break;
            }
          case "System.Double":
            {
              sReturn = "decimal";
              break;
            }
          case "System.Xml":
            {
              sReturn = "xml";
              break;
            }
          default:
            {
              throw new Exception("Unhandled GetDBType Type: " + dataType);
            }
        }
        return sReturn;
    }

    private string ClearDataType(string DataType)
    {
      string sReturn = "";
      switch (DataType)
      {
				case "image":
					{
						sReturn = "null";
						break;
					}
				case "decimal":
				case "money":
					{
						sReturn = "0.0m";
						break;
					}
        case "System.Int16":
          {
            sReturn = "0";
            break;
          }
        case "bigint":
          {
            sReturn = "0";
            break;
          }
				case "smallint":
        case "int":
          {
            sReturn = "0";
            break;
          }
        case "varchar":
        case "char":
        case "text":
          {
            sReturn = "\"\"";
            break;
          }
        case "bit":
          {
            sReturn = "false";
            break;
          }
        case "float":
          {
            sReturn = "0";
            break;
          }
        case "System.Double":
          {
            sReturn = "0";
            break;
          }
        case "datetime":
          {
            sReturn = "new DateTime(1000, 1, 1)";
            break;
          }
        case "System.Xml":
          {
            sReturn = "\"\"";
            break;
          }
        default:
          {
            throw new Exception("Unhandled ClearDataType Type: " + DataType);
          }
      }
      return sReturn;
    }

    #endregion

    #region Load Data
    private void LoadFromStoredProc(string sStoredProcName, string sTableName)
    {
      string[] tableRestrictions = new string[4];
      int iTableCounter = 1, iMaxTables = 0;
      bool bPreviouslyGenerated;
      SetPrmsDBConn();
      //try
      //{
      //GrdGenerate.Rows.Clear();

      // Retrieve the Tables from the schema
      //sourceTables = PRMSSQLConn.;
      //SqlCommand command = new SqlCommand(sStoredProcName, PRMSSQLConn);
      DataSet spColumnDs = this.ExecuteDataSetSQL(sStoredProcName, PRMSSQLConn);
      //command.
      int iRowIndex = 0;
      if (spColumnDs.Tables[0].Rows.Count > 0)
      {
        sourceColumns = PRMSSQLConn.GetSchema("Columns");
        iRowIndex = GrdGenerate.Rows.Add(false, sTableName, null, false, null, false);
        GrdGenerate.Rows[iRowIndex].Tag = sourceColumns;
      }

        //foreach (DataColumn dc in spColumnDs.Tables[0].Columns)
        //{

        //foreach (DataColumn dc in dr[0])
        //{
        //sourceTables = new DataTable();
        //sourceColumns.Columns.Add(dc);
        //dataColumns[0] = dc;
        //sourceColumns.Columns.Add(dc);
        //}
        //PRMSSQLConn.GetSchema("Columns", tableRestrictions);
        //sourceColumns.Columns.Add(dr[
        //}
        //}
        //PRMSSQLConn.
        //  iMaxTables = sourceTables.Rows.Count;
        //  progressBarTables.Maximum = iMaxTables;
        //  this.Cursor = Cursors.WaitCursor;
        //  int iRowIndex = 0;
        //  foreach (DataRow currentTableRow in sourceTables.Rows)
        //  {
        //    tableRestrictions[2] = currentTableRow["TABLE_NAME"].ToString();
        //    sourceColumns = PRMSSQLConn.GetSchema("Columns", tableRestrictions);

        //    //if (CheckForGeneratedObject(currentTableRow["TABLE_NAME"].ToString()))
        //    //  bPreviouslyGenerated = true;
        //    //else
        //    //  bPreviouslyGenerated = false;

        //    //if (bPreviouslyGenerated)
        //    //  btnReGenerate.Enabled = true;

        //    iRowIndex = GrdGenerate.Rows.Add(false, currentTableRow["TABLE_NAME"].ToString(), null, false, null, false);
        //    GrdGenerate.Rows[iRowIndex].Tag = sourceColumns;
        //    iTableCounter++;
        //    if (iTableCounter <= iMaxTables)
        //    {
        //      progressBarTables.Value = iTableCounter;
        //      lblTableCounter.Text = iTableCounter + " of " + iMaxTables;
        //      lblTableCounter.Refresh();
        //      progressBarTables.Refresh();
        //    }
        //  }
        //  this.Cursor = Cursors.Default;
        //  lblTableListInfo.Text = iMaxTables.ToString() + " loaded ...";
        //  lblTableListInfo.Refresh();
        //}
        //catch (Exception runtimeException)
        //{
        //  throw runtimeException;
        //}
        //finally
        //{
        //  if (PRMSSQLConn.State == ConnectionState.Open) { PRMSSQLConn.Close(); }
        //  GrdGenerate.Sort(GrdGenerate.Columns[1], ListSortDirection.Ascending);
        //}
      //}
    }

    private void LoadDBTable()
    {
      string[] tableRestrictions = new string[4];
      int iTableCounter = 1, iMaxTables = 0;
      bool bPreviouslyGenerated;
      SetPrmsDBConn();
      try
      {
				GrdGenerate.Rows.Clear();


        // Retrieve the Tables from the schema
        sourceTables = PRMSSQLConn.GetSchema("Tables");
        iMaxTables = sourceTables.Rows.Count;
        progressBarTables.Maximum = iMaxTables;
        this.Cursor = Cursors.WaitCursor;
        int iRowIndex = 0;
        foreach (DataRow currentTableRow in sourceTables.Rows)
        {
          tableRestrictions[2] = currentTableRow["TABLE_NAME"].ToString();
          sourceColumns = PRMSSQLConn.GetSchema("Columns", tableRestrictions);

          //if (CheckForGeneratedObject(currentTableRow["TABLE_NAME"].ToString()))
          //  bPreviouslyGenerated = true;
          //else
          //  bPreviouslyGenerated = false;

          //if (bPreviouslyGenerated)
          //  btnReGenerate.Enabled = true;

          iRowIndex = GrdGenerate.Rows.Add(false, currentTableRow["TABLE_NAME"].ToString(), null, false, null,false);
          GrdGenerate.Rows[iRowIndex].Tag = sourceColumns;
          iTableCounter++;
          if (iTableCounter <= iMaxTables)
          {
            progressBarTables.Value = iTableCounter;
            lblTableCounter.Text = iTableCounter + " of " + iMaxTables;
            lblTableCounter.Refresh();
            progressBarTables.Refresh();
          }
        }
        this.Cursor = Cursors.Default;
        lblTableListInfo.Text = iMaxTables.ToString() + " loaded ...";
        lblTableListInfo.Refresh();
      }
      catch (Exception runtimeException)
      {
        throw runtimeException;
      }
      finally
      {
        if (PRMSSQLConn.State == ConnectionState.Open) { PRMSSQLConn.Close(); }
        GrdGenerate.Sort(GrdGenerate.Columns[1], ListSortDirection.Ascending);
      }
    }
    #endregion

    #region Save Raptor DB Methods
    private bool SaveRaptorObjectsSettings()
    {
      foreach (KeyValuePair<string, List<List<List<string>>>> table in this.SelectionSetting)
      {
        m_PrimaryKeyColName = "";
        m_ActiveColName = "";
        m_TableName = table.Key;
        int iSettingsCounter = 0;
        // Set Environment Variables
        foreach (List<List<String>> tableColumns in this.SelectionSetting[table.Key])
        {
          switch (iSettingsCounter)
          {
            case 0:
              {
                //Save Generated Object
                m_GenerateObject_ID = SaveGenerateObject(table.Key);
                //Save DO Options
                if (tableColumns != null && tableColumns.Count > 0)
                {
                  SaveDOOptions(tableColumns, m_GenerateObject_ID);
                }
                break;
              }
            case 1:
              {
                //Save Embeded Objects
                if (tableColumns != null && tableColumns.Count > 0)
                {
                  SaveEmbedObjects(tableColumns, m_GenerateObject_ID);
                }
                break;
              }
            case 2:
              {
                //Save Search Parameter
                if (tableColumns != null && tableColumns.Count > 0)
                {
                  SaveSearchParameters(tableColumns, m_GenerateObject_ID);
                }
                break;
              }
            case 3:
              {
                //Save Search Results
                if (tableColumns != null && tableColumns.Count > 0)
                {
                  SaveSearchResults(tableColumns, m_GenerateObject_ID);
                }
                break;
              }
          }
          iSettingsCounter++;
        }
      }
      return false;
    }

    private void SaveDOOptions(List<List<String>> DOOptions, long generateObjectID)
    {
      string sSQLString = "";
      string sCreate = "0", sDelete = "0", sRetrieve = "0", sRetrieveAll = "0", sUpdate = "0",
        sNoLimit = "0", sAudit = "0", sSearchable = "0", sStandard = "0", sStandardDescription = "0";

      string sInsertValuesString = "", sInsertFieldsString = "";

      sInsertFieldsString = "(GenerateObject_ID, DOOption_Create, DOOption_Delete, DOOption_Retrieve, " +
                      "DOOption_RetrieveAll, DOOption_Update, DOOption_NoLimit, DOOption_Audit, " +
                      "DOOption_Searchable, DOOption_StandardLookup, DOOption_StandardLookupDescription)";
      sInsertValuesString = "(" + generateObjectID.ToString() + ", ";

      if (RaptorSQLConn == null)
      {
        SetRaptorDBConn();
      }

      SqlCommand SqlDelete = new SqlCommand("Delete From DOOption Where GenerateObject_ID = " + generateObjectID.ToString(), RaptorSQLConn);
      SqlDelete.ExecuteNonQuery();

      for (int iDOOptionIdx = 0; iDOOptionIdx < DOOptions.Count; iDOOptionIdx++)
      {
        for (int iDOOptionSettingsIdx = 0; iDOOptionSettingsIdx < DOOptions[iDOOptionIdx].Count; iDOOptionSettingsIdx++)
        {
          switch (DOOptions[iDOOptionIdx][iDOOptionSettingsIdx])
          {
            case "CREATE":
              {
                sCreate = "1";
                break;
              }
            case "DELETE":
              {
                sDelete = "1";
                break;
              }
            case "RETRIEVE":
              {
                sRetrieve = "1";
                break;
              }
            case "RETRIEVEALL":
              {
                sRetrieveAll = "1";
                break;
              }
            case "UPDATE":
              {
                sUpdate = "1";
                break;
              }
            case "NOLIMIT":
              {
                sNoLimit = "1";
                break;
              }
            case "AUDIT":
              {
                sAudit = "1";
                break;
              }
            case "SEARCHABLE":
              {
                sSearchable = "1";
                break;
              }
            case "STANDARD":
              {
                sStandard = "1";
                break;
              }
            case "STANDARDDESCRIPTION":
              {
                sStandardDescription = "1";
                break;
              }
          }
        }

        sInsertValuesString += sCreate + ", " + sDelete + ", " + sRetrieve + ", " + sRetrieveAll + ", " + 
                               sUpdate + ", " + sNoLimit + ", " + sAudit + ", " + sSearchable + ", " + 
                               sStandard + ", " + sStandardDescription + ")";
        sSQLString = "INSERT INTO DOOption " + sInsertFieldsString + " VALUES " + sInsertValuesString;

        SqlCommand SQLComm = new SqlCommand(sSQLString, RaptorSQLConn);
        SQLComm.ExecuteNonQuery();
        sInsertValuesString = "(" + generateObjectID.ToString() + ", ";
      }
    }

    private long SaveGenerateObject(string sTableName)
    {
      string sSQLString = "";
      long generateObjectId;
      DataSet DS = LoadGenerateObject(sTableName);

      if (DS.Tables[0].Rows.Count > 0)
      {
        generateObjectId = long.Parse(DS.Tables[0].Rows[0]["GenerateObject_ID"].ToString());
        sSQLString = "UPDATE GenerateObject SET GenerateObject_Name = '" + sTableName + "', GenerateObject_GeneratedBy = '" + thisSettings.UserName + "' WHERE  GenerateObject_ID = " + DS.Tables[0].Rows[0]["GenerateObject_ID"].ToString();
        SqlCommand SQLComm = new SqlCommand(sSQLString, RaptorSQLConn);
        SQLComm.ExecuteNonQuery();
      }
      else
      {
        sSQLString = "INSERT INTO GenerateObject(GenerateObject_Name, GenerateObject_GeneratedBy) VALUES ('" + sTableName + "', '" + thisSettings.UserName + "') SELECT GenerateObject_ID = @@IDENTITY";
        SqlCommand SQLComm = new SqlCommand(sSQLString,RaptorSQLConn);
        generateObjectId = long.Parse(SQLComm.ExecuteScalar().ToString());
      }

      if (RaptorSQLConn ==null)
      {
        SetRaptorDBConn();
      }

      return generateObjectId;
    }

    private void SaveEmbedObjects(List<List<String>> EmbededObjects, long generateObjectID)
    {
      //string sSQLString = "INSERT INTO GenerateObject(GenerateObject_Name) VALUES ('" + sTableName + "') SELECT GenerateObject_ID = @@IDENTITY";
      string sSQLString = "";
      string sInsertValuesString = "", sInsertFieldsString = "";

      sInsertFieldsString = "(GenerateObject_ID, EmbedObject_ReferencedTableName, EmbedObject_ReferencedTableLinkField, " +
                      "EmbedObject_EmbedTableName, EmbedObject_EmbedTableLinkField, EmbedObject_IsObject, EmbedObject_IsList, " +
                      "EmbedObject_IsActive)";
      sInsertValuesString = "(" + generateObjectID.ToString() + ", ";

      if (RaptorSQLConn == null)
      {
        SetRaptorDBConn();
      }

      SqlCommand SqlDelete = new SqlCommand("Delete From EmbedObject Where GenerateObject_ID = " + generateObjectID.ToString(), RaptorSQLConn);
      SqlDelete.ExecuteNonQuery();

      for (int iEmbedIdx = 0; iEmbedIdx < EmbededObjects.Count; iEmbedIdx++)
      {
        for (int iEmbedSettingIdx = 0; iEmbedSettingIdx < EmbededObjects[iEmbedIdx].Count; iEmbedSettingIdx++)
        {
          switch (iEmbedSettingIdx)
          {
            case 0:
            case 1:
            case 2:
            case 3:
              {
                sInsertValuesString +=  "'" + EmbededObjects[iEmbedIdx][iEmbedSettingIdx].ToString() + "', ";
                break;
              }
            case 4:
            case 5:
            case 6:
              {
                string sSQLBool = "";

                if (bool.Parse(EmbededObjects[iEmbedIdx][iEmbedSettingIdx].ToString()))
                  sSQLBool = "1";
                else
                  sSQLBool = "0";

                sInsertValuesString += sSQLBool + ", ";
                break;
              }
          }
        }
        sInsertValuesString = sInsertValuesString.TrimEnd(',',' ') + ")";
        sSQLString = "INSERT INTO EmbedObject " + sInsertFieldsString + " VALUES " + sInsertValuesString;

        SqlCommand SQLComm = new SqlCommand(sSQLString, RaptorSQLConn);
        SQLComm.ExecuteNonQuery();
        sInsertValuesString = "(" + generateObjectID.ToString() + ", ";
      }
    }

    private void SaveSearchParameters(List<List<String>> SearchParameters, long generateObjectID)
    {
      string sSQLString = "";
      string sInsertValuesString = "", sInsertFieldsString = "";

      sInsertFieldsString = "(GenerateObject_ID, SearchParameter_FieldName, SearchParameter_DataType, " +
                      "SearchParameter_FieldLength, SearchParameter_IsNullable, SearchParameter_IsPrimaryKey, SearchParameter_IsSearchParameter, " +
                      "SearchParameter_OrdinalPosition, SearchParameter_IsOptional, SearchParameter_IsRetrieveAll, SearchParameter_IsDependant)";
      sInsertValuesString = "(" + generateObjectID.ToString() + ", ";

      if (RaptorSQLConn == null)
      {
        SetRaptorDBConn();
      }

      SqlCommand SqlDelete = new SqlCommand("Delete From SearchParameter Where GenerateObject_ID = " + generateObjectID.ToString(), RaptorSQLConn);
      SqlDelete.ExecuteNonQuery();

      for (int iSearchParamIdx = 0; iSearchParamIdx < SearchParameters.Count; iSearchParamIdx++)
      {
        for (int iSearchParamSettingIdx = 0; iSearchParamSettingIdx < SearchParameters[iSearchParamIdx].Count; iSearchParamSettingIdx++)
        {
          switch (iSearchParamSettingIdx)
          {
            case 0:
            case 1:
            case 2:
              {
                sInsertValuesString += "'" + SearchParameters[iSearchParamIdx][iSearchParamSettingIdx].ToString() + "', ";
                break;
              }
            case 3:
            case 4:
            case 5:
            case 7:
            case 8:
            case 9:
              {
                string sSQLBool = "";

                if (bool.Parse(SearchParameters[iSearchParamIdx][iSearchParamSettingIdx].ToString()))
                  sSQLBool = "1";
                else
                  sSQLBool = "0";

                sInsertValuesString += sSQLBool + ", ";
                break;
              }
            case 6:
              {
                sInsertValuesString += SearchParameters[iSearchParamIdx][iSearchParamSettingIdx].ToString() + ", ";
                break;
              }
          }
        }
        sInsertValuesString = sInsertValuesString.TrimEnd(',', ' ') + ")";
        sSQLString = "INSERT INTO SearchParameter " + sInsertFieldsString + " VALUES " + sInsertValuesString;
       
        SqlCommand SQLComm = new SqlCommand(sSQLString, RaptorSQLConn);
        SQLComm.ExecuteNonQuery();
        sInsertValuesString = "(" + generateObjectID.ToString() + ", ";
      }
    }

    private void SaveSearchResults(List<List<String>> SearchResults, long generateObjectID)
    {
      string sSQLString = "";
      string sInsertValuesString = "", sInsertFieldsString = "";

      sInsertFieldsString = "(GenerateObject_ID, SearchResult_FieldName)";
      sInsertValuesString = "(" + generateObjectID.ToString() + ", ";

      if (RaptorSQLConn == null)
      {
        SetRaptorDBConn();
      }

      SqlCommand SqlDelete = new SqlCommand("Delete From SearchResult Where GenerateObject_ID = " + generateObjectID.ToString(), RaptorSQLConn);
      SqlDelete.ExecuteNonQuery();
 
      for (int iSearchResultIdx = 0; iSearchResultIdx < SearchResults.Count; iSearchResultIdx++)
      {
        //for (int iSearchResultSettingIdx = 0; iSearchResultSettingIdx < EmbededObjects[iEmbedIdx].Count; iSearchResultSettingIdx++)
        //{
        sInsertValuesString += "'" + SearchResults[iSearchResultIdx][0].ToString() + "')";
        //}
        //sInsertValuesString = sInsertValuesString.TrimEnd(',', ' ') + ")";
        sSQLString = "INSERT INTO SearchResult " + sInsertFieldsString + " VALUES " + sInsertValuesString;
        
        SqlCommand SQLComm = new SqlCommand(sSQLString, RaptorSQLConn);
        SQLComm.ExecuteNonQuery();
        sInsertValuesString = "(" + generateObjectID.ToString() + ", ";
      }
    }

    #endregion

    #region Load Raptor DB Methods
    private DataSet LoadDOOptions(long generateObjectID)
    {
      string sSQLString = "SELECT * FROM DOOption Where GenerateObject_ID = " + generateObjectID.ToString();
      DataSet DS = new DataSet();
      if (RaptorSQLConn == null)
      {
        SetRaptorDBConn();
      }
      SqlDataAdapter adapter = new SqlDataAdapter(sSQLString, RaptorSQLConn);
      adapter.Fill(DS,"DOOption");
      return DS;
    }

    private DataSet LoadGenerateObject(string generateObjectName)
    {
      string sSQLString = "SELECT * FROM GenerateObject Where GenerateObject_Name = '" + generateObjectName + "'";
      DataSet DS = new DataSet();
      if (RaptorSQLConn == null)
      {
        SetRaptorDBConn();
      }
      SqlDataAdapter adapter = new SqlDataAdapter(sSQLString, RaptorSQLConn);
      adapter.Fill(DS, "DOOption");
      return DS;
    }

    private DataSet LoadPreviousGeneratedObjects()
    {
      string sSQLString = "SELECT * FROM GenerateObject";
      DataSet DS = new DataSet();
      if (RaptorSQLConn == null)
      {
        SetRaptorDBConn();
      }
      SqlDataAdapter adapter = new SqlDataAdapter(sSQLString, RaptorSQLConn);
      adapter.Fill(DS, "DOOption");
      return DS;
    }

    private DataSet LoadEmbedObjects(long generateObjectID)
    {
      string sSQLString = "SELECT * FROM EmbedObject Where GenerateObject_ID = " + generateObjectID.ToString();
      DataSet DS = new DataSet();
      if (RaptorSQLConn == null)
      {
        SetRaptorDBConn();
      }
      SqlDataAdapter adapter = new SqlDataAdapter(sSQLString, RaptorSQLConn);
      adapter.Fill(DS, "DOOption");
      return DS;
    }

    private DataSet LoadSearchParameters(long generateObjectID)
    {
      string sSQLString = "SELECT * FROM SearchParameter Where GenerateObject_ID = " + generateObjectID.ToString();
      DataSet DS = new DataSet();
      if (RaptorSQLConn == null)
      {
        SetRaptorDBConn();
      }
      SqlDataAdapter adapter = new SqlDataAdapter(sSQLString, RaptorSQLConn);
      adapter.Fill(DS, "DOOption");
      return DS;
    }

    private DataSet LoadSearchResults(long generateObjectID)
    {
      string sSQLString = "SELECT * FROM SearchResult Where GenerateObject_ID = " + generateObjectID.ToString();
      DataSet DS = new DataSet();
      if (RaptorSQLConn == null)
      {
        SetRaptorDBConn();
      }
      SqlDataAdapter adapter = new SqlDataAdapter(sSQLString, RaptorSQLConn);
      adapter.Fill(DS, "DOOption");
      return DS;
    }

    private bool CheckForGeneratedObject(string generateObjectName)
    {
      DataSet DS = LoadGenerateObject(generateObjectName);

      return (DS.Tables[0].Rows.Count > 0);
    }

    #endregion

    #endregion

    #region TemplateMethods
    private string GetTemplateText(string templateName)
    {
      string spText = "";

      #region Get the Template

      using (StreamReader sr = new StreamReader(Application.StartupPath + @"\Templates\" + templateName))
      {
        string line;
        while ((line = sr.ReadLine()) != null)
        {
          line = line + "\r\n";
          spText = spText + line;
        }
      }

      #endregion

      return (spText);
    }
    #endregion

    #region StoredProcs Methods

    private void CreateStoredProcs()
    {
      CreateSPCreate();
      CreateSPDelete();
      CreateSPRetrieve();
      CreateSPUpdate();
      CreateSPRetrieveAll();
    }

    #region Create Methods

    private void CreateSPCreate()
    {
      string spText = "";

      spText = GetTemplateText("spCreateTemplate.txt");

      string paramList = GetParameterList(false);
      string insertCols = GetInsertColNameList();
      string insertParams = GetInsertParamList();

      spText = spText.Replace("|SPPARAMTERES|", paramList);
      spText = spText.Replace("|TABLECOLUMNS|", insertCols);
      spText = spText.Replace("|PARAMSLIST|", insertParams);

      spText = spText.Replace("|TABLENAME|", m_TableName);

      spText = spText.Replace("|DATE|", DateTime.Now.ToShortDateString());

      txtSPCreate.Text = spText;
    }

    private string GetInsertColNameList()
    {
      string returnString = "";

      for(int iParamIdx = 0; iParamIdx < SystemOptions.CurrentTableFields.Count;iParamIdx++)
      {
        if (SystemOptions.CurrentTableFields[iParamIdx] != null)
        {
          if (Convert.ToBoolean(SystemOptions.CurrentTableFields[iParamIdx][4]))
          {
            continue;
          }

          if (SystemOptions.CurrentTableFields[iParamIdx][0] == m_ActiveColName)
          {
            continue;
          }

          if (returnString != "")
          {
            returnString = returnString + ",\r\n\t\t\t";
          }
          returnString = returnString + SystemOptions.CurrentTableFields[iParamIdx][0];

        }
      }

      return (returnString);
    }

    private string GetInsertParamList()
    {
      string returnString = "";

      for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentTableFields.Count; iParamIdx++)
      {
        if (SystemOptions.CurrentTableFields[iParamIdx] != null)
        {
          if (Convert.ToBoolean(SystemOptions.CurrentTableFields[iParamIdx][4]))
          {
            continue;
          }

          if (SystemOptions.CurrentTableFields[iParamIdx][0] == m_ActiveColName)
          {
            continue;
          }

          if (returnString != "")
          {
            returnString = returnString + ",\r\n\t\t\t";
          }
          returnString = returnString + "@" + SystemOptions.CurrentTableFields[iParamIdx][0];

        }
      }

      return (returnString);
    }

    #endregion

    #region Delete Methods
    private void CreateSPDelete()
    {
      // Ensure the Table has a Column for inactive

      string spText = "";

      if (m_ActiveColName == "")
        spText = GetTemplateText("spDeleteAllTemplate.txt");
      else
        spText = GetTemplateText("spDeleteTemplate.txt");

      spText = spText.Replace("|TABLENAME|", m_TableName);
      spText = spText.Replace("|PRIMARYKEY|", m_PrimaryKeyColName);
      spText = spText.Replace("|PRIMARYKEYTYPE|", m_PrimaryKeyType);
      spText = spText.Replace("|ACTIVECOLNAME|", m_ActiveColName);
      spText = spText.Replace("|DATE|", DateTime.Now.ToShortDateString());

      txtSPDelete.Text = spText;
    }
    #endregion

    #region UpdateMethods

    private void CreateSPUpdate()
    {
      string spText = "";

      spText = GetTemplateText("spUpdateTemplate.txt");

      string paramList = GetParameterList(true);
      string setList = GetUpdateSetList();

      spText = spText.Replace("|SPPARAMTERES|", paramList);
      spText = spText.Replace("|SETLIST|", setList);
      spText = spText.Replace("|TABLENAME|", m_TableName);
      spText = spText.Replace("|PRIMARYKEY|", m_PrimaryKeyColName);
      spText = spText.Replace("|PRIMARYKEYTYPE|", m_PrimaryKeyType);

      spText = spText.Replace("|DATE|", DateTime.Now.ToShortDateString());

      txtSPUpdate.Text = spText;
    }

    private string GetUpdateSetList()
    {
      string returnString = "";

      for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentTableFields.Count; iParamIdx++)
      {
        if (SystemOptions.CurrentTableFields[iParamIdx] != null)
        {
          if (!Convert.ToBoolean(SystemOptions.CurrentTableFields[iParamIdx][4]))
          {
            string sColName = SystemOptions.CurrentTableFields[iParamIdx][0];

            if (sColName.ToUpper() != m_TableName.ToString().ToUpper() + "_ACTIVE")
            {
              if (returnString != "")
                returnString = returnString + ", \r\n\t\t";

              returnString = returnString + sColName + " = @" + sColName;
            }
            else
            {
              if (returnString != "")
                returnString = returnString + ", \r\n\t\t";

              returnString = returnString + sColName + " = 1";
            }
          }
        }
      }

      return (returnString);
    }

    #endregion

    #region Retrieve Methods
    private void CreateSPRetrieve()
    {
      string spText = "";

      spText = GetTemplateText("spRetrieveTemplate.txt");

      spText = spText.Replace("|TABLENAME|", m_TableName);
      spText = spText.Replace("|DATE|", DateTime.Now.ToShortDateString());

      txtSPRetrieve.Text = spText;
    }
    #endregion

    #region RetrieveAll Methods

    private void CreateSPRetrieveAll()
    {
      if (!SystemOptions.DynamicQueries)
      {
        CreateSPRetrieveAllNonDynamic();
        return;
      }
      string spText = "";

      spText = GetTemplateText("spRetrieveAllTemplate.txt");

      string spParams = GetRetieveAllSPParams();
      string whereClause = GetRetrieveAllWhereStatement();

      //if (chkLimit.Checked)
      if (SystemOptions.Limit)
      {
        string limit = GetTemplateText("spMaxRecordsTemplate.txt");
        spText = spText.Replace("|MAXRECORDS|", SystemOptions.LimitTo);
        spText = spText.Replace("|TOP|", "TOP ' + CAST(@MaxResults as varchar) + '");
      }
      else
      {
        spText = spText.Replace("|MAXRECORDS|", "");
        spText = spText.Replace("|TOP|", "");
      }

      spText = spText.Replace("|SPPARAMTERES|", spParams);
      spText = spText.Replace("|WHERELIST|", whereClause);


      spText = spText.Replace("|TABLENAME|", m_TableName);
      spText = spText.Replace("|DATE|", DateTime.Now.ToShortDateString());

      txtSPRetrieveAll.Text = spText;
    }

    private void CreateSPRetrieveAllNonDynamic()
    {
      string spText = "";

      spText = GetTemplateText("spRetrieveAllNonDynamicTemplate.txt");

      string spParams = GetRetieveAllSPParams();
      string spJoinList = GetJoinList();
      string whereClause = GetRetrieveAllWhereStatement() + "\r\n";

      spText = spText.Replace("|SPPARAMTERES|", spParams);
      spText = spText.Replace("|JOINLIST|", spJoinList);
      spText = spText.Replace("|WHERELIST|", whereClause);

      if (SystemOptions.Limit)
      {
        string limit = GetTemplateText("spMaxRecordsTemplate.txt").TrimStart('\t');
        spText = spText.Replace("\t|MAXRECORDS|", "\t" + limit + "\r\n");
        spText = spText.Replace("\t|MAXROWCOUNT|", "\tSET ROWCOUNT @MaxResults\r\n\r\n");
        spText = spText.Replace("\t|ROWCOUNTRESET|", "\tSET ROWCOUNT 0\r\n\r\n");
      }
      else
      {
        spText = spText.Replace("\t|MAXRECORDS|", "");
        spText = spText.Replace("\t|MAXROWCOUNT|", "");
        spText = spText.Replace("\t|ROWCOUNTRESET|", "");
      }

      spText = spText.Replace("|TABLENAME|", m_TableName);
      spText = spText.Replace("|DATE|", DateTime.Now.ToShortDateString());

      txtSPRetrieveAll.Text = spText;
    }

    private string GetRetieveAllSPParams()
    {
      string returnString = "";

      //foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
      //{
      //  if (Convert.ToBoolean(thisRow.Cells[5].Value))
      //  {
      for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentTableFields.Count; iParamIdx++)
      {
        if (SystemOptions.CurrentTableFields[iParamIdx] != null)
        {
          if (Convert.ToBoolean(SystemOptions.CurrentTableFields[iParamIdx][8]))
          {
            string sColName = SystemOptions.CurrentTableFields[iParamIdx][0];

            string sDBType = SystemOptions.CurrentTableFields[iParamIdx][1];
            string slength = SystemOptions.CurrentTableFields[iParamIdx][2];
            string sParamType = GetDBType(sDBType, slength);
            if (sParamType.ToLower() == "bit") sParamType = "numeric";

            if (returnString != "")
              returnString = returnString + ", \r\n\t\t";

            returnString = returnString + "@" + sColName + " " + sParamType + " = null";
          }
        }
      }

      if (returnString.Length > 0)
      {
        if (SystemOptions.CurrentDOOptions[0].Count > 6 && SystemOptions.CurrentDOOptions[0][6] == "STANDARD")
        {
          if (SystemOptions.CurrentDOOptions[0][6] == "STANDARD")
          {
            returnString += ", \r\n\t\t@Description varchar(200) = null";

          }
        }
        returnString = "(\r\n\t\t" + returnString + "\r\n\t)";
      }

      return returnString;
    }

    private string GetRetrieveAllWhereStatement()
    {
      string returnString = "";

      for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentTableFields.Count; iParamIdx++)
      {
        if (SystemOptions.CurrentTableFields[iParamIdx] != null)
        {
          if (Convert.ToBoolean(SystemOptions.CurrentTableFields[iParamIdx][8]))
          {
            string sColName = SystemOptions.CurrentTableFields[iParamIdx][0];

            string whereText = "";

            if (SystemOptions.DynamicQueries)
            {
              // Data Type to determine wheter casting is necessarry.
              bool mustCast = false;
              string dataType = SystemOptions.CurrentTableFields[iParamIdx][2];
              if (dataType.ToLower() == "long" || dataType.ToLower() == "int" || dataType.ToLower() == "decimal")
              {
                mustCast = true;
              }

              if (Convert.ToBoolean(SystemOptions.CurrentTableFields[iParamIdx][4]))
              {
                whereText = whereText + "\r\n\t";
                whereText = whereText + "IF @|FIELDNAME| IS NOT NULL";
                whereText = whereText + "\r\n\t BEGIN";
                whereText = whereText + "\r\n\t\t";
                if (mustCast)
                {
                  whereText = whereText + "SET @Where = @Where + 'AND |TABLENAME|.|FIELDNAME| like ''%' + CAST(@|FIELDNAME| AS VARCHAR) + '%'' '";
                }
                else
                {
                  whereText = whereText + "SET @Where = @Where + 'AND |TABLENAME|.|FIELDNAME| like ''%' + @|FIELDNAME| + '%'' '";
                }
                whereText = whereText + "\r\n\t END";

                //whereText = GetTemplateText("RetrieveAllLikeItemTemplate.txt");
              }
              else
              {
                whereText = whereText + "\r\n\t";
                whereText = whereText + "IF @|FIELDNAME| IS NOT NULL";
                whereText = whereText + "\r\n\t BEGIN";
                whereText = whereText + "\r\n\t\t";
                if (mustCast)
                {
                  whereText = whereText + "SET @Where = @Where + 'AND |TABLENAME|.|FIELDNAME| = ' + CAST(@|FIELDNAME| AS VARCHAR) + ' '    ";
                }
                else
                {
                  whereText = whereText + "SET @Where = @Where + 'AND |TABLENAME|.|FIELDNAME| = ' + @|FIELDNAME| + ' '    ";
                }
                whereText = whereText + "\r\n\t END";
                //whereText = GetTemplateText("RetrieveAllItemTemplate.txt");
              }
            }
            else
            {
              /*
              if (Convert.ToBoolean(SystemOptions.DynamicQueries))
              {
                whereText = "|TABLENAME|.|FIELDNAME| like ISNULL('%' + @|FIELDNAME| + '%',|TABLENAME|.|FIELDNAME|)";
              }
              else
              {
                whereText = "|TABLENAME|.|FIELDNAME| = ISNULL(@|FIELDNAME|,|TABLENAME|.|FIELDNAME|)";
              }
              */

              if (SystemOptions.CurrentTableFields[iParamIdx][1].ToString().ToLower() == "varchar")
              {
                whereText = "(@|FIELDNAME| IS NULL OR |TABLENAME|.|FIELDNAME| LIKE @|FIELDNAME| + '%')";
              }
              else
              {
                whereText = "(@|FIELDNAME| IS NULL OR |TABLENAME|.|FIELDNAME| = @|FIELDNAME|)";
              }
            }

            whereText = whereText.Replace("|FIELDNAME|", sColName);

            if (!SystemOptions.DynamicQueries)
            {
              if (returnString != "")
              {
                returnString = returnString + "\r\n\t  AND\t";
              }
            }
            else
            {
              if (returnString != "")
              {
                returnString = returnString + "\r\n\t\t  ";
              }
            }

            returnString = returnString + whereText;
          }
        }
      }

      if (returnString.Length > 0)
      {
        if (SystemOptions.CurrentDOOptions[0].Count > 6)
        {
          if (SystemOptions.CurrentDOOptions[0][6] == "STANDARD")
          {
            string sLookUpWhere = "\r\n\t\tAND (@Description IS NULL OR |TABLENAME|Description.|TABLENAME|Description_Description LIKE @Description + '%')";
            sLookUpWhere += "\r\n\t\tAND (|TABLENAME|Description.Language_ID = (SELECT Language_ID FROM [Language] WHERE Language_Default = 1))";

            returnString += sLookUpWhere;
          }
        }
        returnString = "WHERE\t" + returnString;
      }

      return (returnString);
    }

    private string GetJoinList()
    {
      // Determine whether a standard lookup is being generated
      if (SystemOptions.CurrentDOOptions[0].Count > 6 && SystemOptions.CurrentDOOptions[0][6] == "STANDARD")
      {
        return "LEFT JOIN |TABLENAME|Description ON |TABLENAME|Description.|TABLENAME|_ID = |TABLENAME|.|TABLENAME|_ID";
      }
      else
      {
        return string.Empty;
      }
    }

    #endregion

    #region All Support methods

    private string GetParameterList(bool includeID)
    {
      string returnString = "";

      for(int iParamIdx = 0; iParamIdx < SystemOptions.CurrentTableFields.Count;iParamIdx++)
      {
        if (SystemOptions.CurrentTableFields[0] != null)
        {
          string sColName = SystemOptions.CurrentTableFields[iParamIdx][0];

          if (sColName.ToUpper() != m_TableName.ToString().ToUpper() + "_ACTIVE")
          {
            string sDBType = SystemOptions.CurrentTableFields[iParamIdx][1];
            string slength = SystemOptions.CurrentTableFields[iParamIdx][2];
            string sParamType = GetDBType(sDBType, slength);

            // Exclude Active
            // if (sColName.ToUpper() == m_TableName.ToString().ToUpper() + "_ACTIVE")
            //   continue;

            if (Convert.ToBoolean(SystemOptions.CurrentTableFields[iParamIdx][4]))
            {
              if (!includeID)
                continue;
            }

            if (returnString != "")
              returnString = returnString + ", \r\n\t\t";

            string defaultNull = "";
            if (Convert.ToBoolean(SystemOptions.CurrentTableFields[iParamIdx][3]))
              defaultNull = " = null";

            returnString = returnString + "@" + sColName + " " + sParamType + defaultNull;
          }

        }
      }

      return (returnString);
      //return "";
    }

    private string GetFriendlyFieldName(string originalName)
    {
      string returnString;
      returnString = originalName.Replace(m_TableName + "_", "");
      // Ensure IDs are camel case
      if (returnString.Length > 2)
      {
        if ((returnString.Substring(returnString.Length - 3, 3) == "_ID") || (returnString.Substring(returnString.Length - 3, 3) == "_Id"))
          returnString = returnString.Substring(0, returnString.Length - 3) + "Id";
      }
      if (returnString == "ID") returnString = "Id";
      return (returnString);
    }

    private string GetFriendlyFieldNameWithSpaces(string originalName)
    {
      string fieldFriendlyName = GetFriendlyFieldName(originalName);

      // Make the fieldFriendlyName camel case
      for (int i = 0; i < fieldFriendlyName.Length - 1; i++)
      {
        int j = 0;

        // Determine whether these two characters are upper case
        if (Char.IsLetter(fieldFriendlyName[i]) && fieldFriendlyName.Substring(i, 2).ToUpper() == fieldFriendlyName.Substring(i, 2))
        {
          // Find the last character that is uppercase
          for (j = i; j < fieldFriendlyName.Length - 1; j++)
          {
            // Determine whether this character is lower case
            if (fieldFriendlyName.Substring(j + 1, 1).ToLower() == fieldFriendlyName.Substring(j + 1, 1))
            {
              // Stop searching
              break;
            }
          }

          fieldFriendlyName = fieldFriendlyName.Substring(0, i + 1) + fieldFriendlyName.Substring(i + 1, j - i).ToLower() + fieldFriendlyName.Substring(j + 1);

          i = j;
        }
      }

      // Replace the underscores with spaces
      fieldFriendlyName = fieldFriendlyName.Replace("_", " ");

      // Put spaces in between words starting with upper case characters
      for (int i = 1; i < fieldFriendlyName.Length; i++)
      {
        // Determine whether this character is upper case
        if (Char.IsLetter(fieldFriendlyName[i]) && fieldFriendlyName.Substring(i, 1).ToUpper() == fieldFriendlyName.Substring(i, 1))
        {
          // Determine whether this character has no space in front of it
          if (fieldFriendlyName.Substring(i - 1, 1) != " ")
          {
            // Insert the space character
            fieldFriendlyName = fieldFriendlyName.Substring(0, i) + " " + fieldFriendlyName.Substring(i);
          }
        }
      }

      return fieldFriendlyName;
    }

    private string GetPluralName(string originalName)
    {
      string returnString = string.Empty;

      if (originalName.Length > 0)
      {
				if ((originalName.EndsWith("s", StringComparison.CurrentCultureIgnoreCase)) && (!originalName.EndsWith("ss", StringComparison.CurrentCultureIgnoreCase)))
				{
					returnString = originalName;
				}
				else
				{
					if (originalName.EndsWith("y", StringComparison.CurrentCultureIgnoreCase))
					{
						returnString = originalName.Substring(0, originalName.Length - 1) + "ies";
					}
					else
					{
						if ((originalName.EndsWith("h", StringComparison.CurrentCultureIgnoreCase)) || (originalName.EndsWith("ss", StringComparison.CurrentCultureIgnoreCase)))
						{
							returnString = originalName + "es";
						}
						else
						{
							returnString = originalName + "s";
						}
					}
				}
      }

      return (returnString);
    }

    #endregion

    #endregion

    #region DO Methods

    private void CreateDO(string tableName)
    {
      string spText = "";

      spText = GetTemplateText("DOTemplate.txt");
      //string inheritValue = GetInheritValue();
      //string queryData = GetAFRSQueryData();
      //string queryParameter = GetAFRSQueryParameters();
      //string doEmbedded = GetEmbeddedList();
      string constantList = GetConstantList();
      string privateVariableList = GetDOPrivateVariableList();
      string publicProperties = GetDOPropertyList();
      string setClassProperties = GetClassPropertiesForSet();
      string setClassPropertiesList = GetClassPropertiesListForSet();
      //
      //string clearList = GetDOClearVariableList();

      #region Set Namespace on DO

      spText = spText.Replace("|NAMESPACE|", txtNSDO.Text);

      #endregion

      #region Set Stored Proc Names depending on DO Selection

      string spCreate = "";
      //if (chkCreate.Checked)
      spCreate = "sp|TABLENAME|_Create";
      string spUpdate = "";
      //if (chkUpdate.Checked)
      spUpdate = "sp|TABLENAME|_Update";
      string spRetrieve = "";
      //if (chkRetrieve.Checked)
      spRetrieve = "sp|TABLENAME|_Retrieve";
      string spRetrieveAll = "";
      //if (chkRetrieveAll.Checked)
      spRetrieveAll = "sp|TABLENAME|_RetrieveAll";
      string spDelete = "";
      //if (chkDelete.Checked)
      spDelete = "sp|TABLENAME|_Delete";

      spText = spText.Replace("|SPCREATE|", spCreate);
      spText = spText.Replace("|SPDELETE|", spDelete);
      spText = spText.Replace("|SPRETRIEVE|", spRetrieve);
      spText = spText.Replace("|SPRETRIEVEALL|", spRetrieveAll);
      spText = spText.Replace("|SPUPDATE|", spUpdate);

      #endregion

      #region Set Can options on DO

      //spText = spText.Replace("|CANCREATE|", chkCreate.Checked.ToString().ToLower());
      //spText = spText.Replace("|CANUPDATE|", chkUpdate.Checked.ToString().ToLower());
      //spText = spText.Replace("|CANRETRIEVEALL|", chkRetrieveAll.Checked.ToString().ToLower());
      //spText = spText.Replace("|CANDELETE|", chkDelete.Checked.ToString().ToLower());

      #endregion

      //spText = spText.Replace("|INHERIT|", inheritValue);
      //spText = spText.Replace("|AFRSQUERYDATA|", queryData);
      //spText = spText.Replace("|AFRSQUERYPARAMETER|", queryParameter);
      spText = spText.Replace("|CONSTANTLIST|", constantList);
      spText = spText.Replace("|PRIVATEVARIABLELIST|", privateVariableList);
      spText = spText.Replace("|PUBLICPROPERTYLIST|", publicProperties);
      spText = spText.Replace("|SETCLASSPROPERTIES|", setClassProperties);
      spText = spText.Replace("|SETCLASSPROPERTIESLIST|", setClassPropertiesList);
      spText = spText.Replace("|TABLEFRIENDLYNAMEWITHSPACES|", GetFriendlyFieldNameWithSpaces(tableName));
      spText = spText.Replace("|TABLENAME|", tableName);
      spText = spText.Replace("|PRIMARYKEY|", m_PrimaryKeyColName);

      txtDO.Text = spText;

      if (CheckIfStandardLookupDescription())
      {
        CreateDOPartialForLookupDescription();
      }
      else
      {
        txtDOPartial.Text = "";
      }
    }

    private string GetConstantList()
    {
      string returnString = "";

      for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentTableFields.Count; iParamIdx++)
      {
        if (SystemOptions.CurrentTableFields[0] != null)
        {
          string columnName = SystemOptions.CurrentTableFields[iParamIdx][0];
          string friendlyFieldName = GetFriendlyFieldName(columnName);

          string constTemplate = GetTemplateText("DOConstItem.txt");
          constTemplate = constTemplate.Replace("|FIELDFRIENDLYNAME|", friendlyFieldName);
          constTemplate = constTemplate.Replace("|UPPERFIELDFRIENDLYNAME|", friendlyFieldName.ToUpper());
          constTemplate = constTemplate.Replace("|FIELDNAME|", columnName);

          if (returnString.Length > 0) returnString += "\r\n";
          returnString = returnString + constTemplate;

        }
      }
      returnString = returnString.Substring(0, returnString.Length - 2);

      string sSPString = "\r\n\r\n#region Stored Procedures\r\n";

      for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentDOOptions[0].Count - 1; iParamIdx++)
      {
        if (SystemOptions.CurrentDOOptions[0] != null)
        {
          //string columnName = SystemOptions.CurrentDOOptions[iParamIdx][0];
          //string friendlyFieldName = GetFriendlyFieldName(columnName);

          //string constTemplate = GetTemplateText("DOConstItem.txt");
          //constTemplate = constTemplate.Replace("|FIELDFRIENDLYNAME|", friendlyFieldName);
          //constTemplate = constTemplate.Replace("|UPPERFIELDFRIENDLYNAME|", friendlyFieldName.ToUpper());
         // constTemplate = constTemplate.Replace("|FIELDNAME|", columnName);
          sSPString += "private const string m_StoredProcedureConst" + CapFirstLetter(SystemOptions.CurrentDOOptions[0][iParamIdx]) + " = \"sp|TABLENAME|_" + CapFirstLetter(SystemOptions.CurrentDOOptions[0][iParamIdx]) + "\";";
          if (returnString.Length > 0) returnString += "\r\n";
          returnString = returnString + sSPString;

          sSPString = "";
        }
      }
      returnString += "\r\n#endregion";
      return returnString;
    }

    private string GetClassPropertiesForSet()
    {
      string returnString = "";

      for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentTableFields.Count; iParamIdx++)
      {
        if (SystemOptions.CurrentTableFields[0] != null)
        {
          string columnName = SystemOptions.CurrentTableFields[iParamIdx][0];
          string friendlyFieldName = GetFriendlyFieldName(columnName);

          string PropertyForSet = GetTemplateText("SetPropertiesTemplate.txt");
          PropertyForSet = PropertyForSet.Replace("|FIELDFRIENDLYNAME|", friendlyFieldName);
          if (GetAppDataType(SystemOptions.CurrentTableFields[iParamIdx][1], SystemOptions.CurrentTableFields[iParamIdx][2]) != "string")
          {
            PropertyForSet = PropertyForSet.Replace("|PARSEOPEN|", GetAppDataType(SystemOptions.CurrentTableFields[iParamIdx][1], SystemOptions.CurrentTableFields[iParamIdx][2]) + ".Parse(");
            PropertyForSet = PropertyForSet.Replace("|PARSECLOSE|", ")");
          }
          else
          {
            PropertyForSet = PropertyForSet.Replace("|PARSEOPEN|", "");
            PropertyForSet = PropertyForSet.Replace("|PARSECLOSE|", "");
          }
          PropertyForSet = PropertyForSet.Replace("|POSITION|", "this");

          if (returnString.Length > 0) returnString += "\r\n";
          returnString = returnString + PropertyForSet;

        }
      }
      returnString = returnString.Substring(0, returnString.Length - 2);
      return returnString;
    }

    private string GetClassPropertiesListForSet()
    {
      string returnString = "";

      for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentTableFields.Count; iParamIdx++)
      {
        if (SystemOptions.CurrentTableFields[0] != null)
        {
          string columnName = SystemOptions.CurrentTableFields[iParamIdx][0];
          string friendlyFieldName = GetFriendlyFieldName(columnName);

          string PropertyForSet = GetTemplateText("SetPropertiesTemplate.txt");
          PropertyForSet = PropertyForSet.Replace("|FIELDFRIENDLYNAME|", friendlyFieldName);
          if (GetAppDataType(SystemOptions.CurrentTableFields[iParamIdx][1], SystemOptions.CurrentTableFields[iParamIdx][2]) != "string")
          {
            PropertyForSet = PropertyForSet.Replace("|PARSEOPEN|", GetAppDataType(SystemOptions.CurrentTableFields[iParamIdx][1], SystemOptions.CurrentTableFields[iParamIdx][2]) + ".Parse(");
            PropertyForSet = PropertyForSet.Replace("|PARSECLOSE|", ")");
          }
          else
          {
            PropertyForSet = PropertyForSet.Replace("|PARSEOPEN|", "");
            PropertyForSet = PropertyForSet.Replace("|PARSECLOSE|", "");
          }
          PropertyForSet = PropertyForSet.Replace("|POSITION|", "|TABLENAME|");

          if (returnString.Length > 0) returnString += "\r\n";
          returnString = returnString + PropertyForSet;

        }
      }
      returnString = returnString.Substring(0, returnString.Length - 2);
      return returnString;
    }

    private string GetInheritValue()
    {
      string returnString = "";
      bool bStandardLookup, bStandardLookupDescription;

      if (SystemOptions.CurrentDOOptions[0] != null)
      {
        bStandardLookup = false;
        bStandardLookupDescription = false;
        for (int iParamIdx = 5;iParamIdx < SystemOptions.CurrentDOOptions[0].Count;iParamIdx++)
        {
          switch(SystemOptions.CurrentDOOptions[0][iParamIdx])
          {
            case "STANDARD":
              {
                bStandardLookup = true;
                break;
              }
            case "STANDARDDESCRIPTION":
              {
                bStandardLookupDescription = true;
                break;
              }
          }
        }

        if (bStandardLookup)
        {
          //INHERIT = StandardLookup
          returnString = "AfrsStandardLookupDataObjectBase<DO|TABLENAME|Description>";
        }
        else if (bStandardLookupDescription)
        {
          //INHERIT = StandardLookupDescription\
          returnString = "AfrsStandardLookupDescriptionDataObjectBase";
        }
        else
        {
          //INHERIT = Defualt
          returnString = "AfrsDataObjectBase";
        }
      }
      return returnString;
    }

    private string GetPublicOverrideValue(string columnName, string sParent)
    {
      string returnString = "";
      bool bStandardLookup, bStandardLookupDescription;

      if (SystemOptions.CurrentDOOptions[0] != null)
      {
        bStandardLookup = false;
        bStandardLookupDescription = false;
        for (int iParamIdx = 5; iParamIdx < SystemOptions.CurrentDOOptions[0].Count; iParamIdx++)
        {
          switch (SystemOptions.CurrentDOOptions[0][iParamIdx])
          {
            case "STANDARD":
              {
                bStandardLookup = true;
                break;
              }
            case "STANDARDDESCRIPTION":
              {
                bStandardLookupDescription = true;
                break;
              }
          }
        }

        if (bStandardLookup || bStandardLookupDescription)
        {
          //INHERIT = StandardLookup
          if (bStandardLookupDescription)
          {
            if (columnName == sParent + "_ID")
            {
              returnString = "";
            }
            else
            {
              returnString = "override ";
            }
          }
          else
          {
            returnString = "override ";
          }
        }
        else
        {
          //INHERIT = Defualt
          returnString = "";
        }
      }
      return returnString;
    }

    private bool CheckIfStandardLookupDescription()
    {
      bool returnValue = false;
      bool bStandardLookup, bStandardLookupDescription;

      if (SystemOptions.CurrentDOOptions[0] != null)
      {
        bStandardLookup = false;
        bStandardLookupDescription = false;
        for (int iParamIdx = 5; iParamIdx < SystemOptions.CurrentDOOptions[0].Count; iParamIdx++)
        {
          switch (SystemOptions.CurrentDOOptions[0][iParamIdx])
          {
            case "STANDARD":
              {
                bStandardLookup = true;
                break;
              }
            case "STANDARDDESCRIPTION":
              {
                bStandardLookupDescription = true;
                break;
              }
          }
        }

        if (bStandardLookupDescription)
        {
          returnValue = true;
        }
        else
        {
          returnValue = false;
        }
      }
      return returnValue;
    }

    private string GetAFRSQueryData()
    {
      string sQueryData = "";
      string sStoredProc = "";
      string returnString = "";
      int iPadChars = 0;

      for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentDOOptions[0].Count; iParamIdx++)
      {
        switch (SystemOptions.CurrentDOOptions[0][iParamIdx].ToString())
        {
          case "CREATE":
          case "DELETE":
          case "RETRIEVE":
          case "RETRIEVEALL":
          case "UPDATE":
            {
              if (SystemOptions.CurrentDOOptions[0][iParamIdx].ToString() == "RETRIEVE")
                iPadChars = 18;
              else if (SystemOptions.CurrentDOOptions[0][iParamIdx].ToString() == "RETRIEVEALL")
                iPadChars = 15;
              else
                iPadChars = 20;

              sQueryData = "\t[AfrsQueryData(QueryDataType." + CamelCase(SystemOptions.CurrentDOOptions[0][iParamIdx].ToString().ToLower()).Replace("all","All") + ",".PadRight(iPadChars, ' ');
              sStoredProc = "StoredProcedure = \"sp|TABLENAME|_" + CamelCase(SystemOptions.CurrentDOOptions[0][iParamIdx].ToString().ToLower()).Replace("all", "All") + "\")]";
              
              if (returnString.Length > 0) returnString += "\r\n";
              returnString += sQueryData + sStoredProc;
              break;
            }
        }
      }

      return (returnString);
    }

    private string GetAFRSQueryParameters()
    {
      string sParamName = "";
      string sCreateOptions = "\r\n";
      string returnString = "";

      for (int iPropIdx = 0; iPropIdx < SystemOptions.CurrentTableFields.Count; iPropIdx++)
      {
        string friendlyName = GetFriendlyFieldName(SystemOptions.CurrentTableFields[iPropIdx][0]);
        sParamName = "\t[AfrsQueryParameter(\"" + friendlyName + "\", " +
                     GetSystemDataType(SystemOptions.CurrentTableFields[iPropIdx][1]) + ", \"@\" + DO|TABLENAME|." +
                     "Row" + friendlyName;

        for (int iOptionIdx = 0; iOptionIdx < SystemOptions.CurrentDOOptions[0].Count; iOptionIdx++)
        {
          string doOptionCamelCaseName = CamelCase(SystemOptions.CurrentDOOptions[0][iOptionIdx].ToString().ToLowerInvariant());
          bool applyAttribute = false;
          bool applyAttributeMandatory = applyAttributeMandatory = !Convert.ToBoolean(SystemOptions.CurrentTableFields[iPropIdx][3]);

          switch (SystemOptions.CurrentDOOptions[0][iOptionIdx].ToString())
          {
            case "CREATE":
              {
                applyAttribute = !Convert.ToBoolean(SystemOptions.CurrentTableFields[iPropIdx][4]) && SystemOptions.CurrentTableFields[iPropIdx][0].ToUpper() != m_TableName.ToString().ToUpper() + "_ACTIVE";
                break;
              }
            case "DELETE":
              {
                applyAttribute = Convert.ToBoolean(SystemOptions.CurrentTableFields[iPropIdx][4]);
                break;
              }
            case "RETRIEVE":
              {
                applyAttribute = Convert.ToBoolean(SystemOptions.CurrentTableFields[iPropIdx][4]);
                break;
              }
            case "UPDATE":
              {
                applyAttribute = SystemOptions.CurrentTableFields[iPropIdx][0].ToUpper() != m_TableName.ToString().ToUpper() + "_ACTIVE";
                break;
              }
            case "RETRIEVEALL":
              {
                applyAttribute = Convert.ToBoolean(SystemOptions.CurrentTableFields[iPropIdx][8]);
                applyAttributeMandatory = false;
                doOptionCamelCaseName = "RetrieveAll";
                break;
              }
          }

          if(applyAttribute)
          {
            sCreateOptions += ",\r\n";
            sCreateOptions += "\t\tIs" + doOptionCamelCaseName + "Parameter = true";

            if (applyAttributeMandatory)
            {
              sCreateOptions += ", ";
              sCreateOptions += "\t\tIs" + doOptionCamelCaseName + "Mandatory = true";
            }
          }
        }

        if (returnString.Length > 0) returnString += "\r\n";

        returnString += sParamName;

        if (sCreateOptions != "\r\n")
        {
          returnString += ", " + sCreateOptions.Substring(3, sCreateOptions.Length - 3) + ")]";
        }
        else
        {
          returnString += ")]";
        }

        sParamName = "";
        sCreateOptions = "\r\n";
      }
      return returnString;
    }

    private string GetEmbeddedList()
    {
      string returnString = "";
      bool bIsList = false;
      string sEmbedActive = "";
      if (SystemOptions.CurrentEmbedObjects != null)
      {
        for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentEmbedObjects.Count; iParamIdx++)
        {
          if (SystemOptions.CurrentEmbedObjects[iParamIdx][4] == "true")
          {
            if (SystemOptions.CurrentEmbedObjects[iParamIdx][5] == "true")
              bIsList = true;
            else
              bIsList = false;

            if (SystemOptions.CurrentEmbedObjects[iParamIdx][6] == "false")
            {
              sEmbedActive = "";
            }
            else
            {
              sEmbedActive = "//";
            }

            if (returnString.Length > 0) returnString += "\r\n";
            //for (int iPropIdx = 0; iPropIdx < SystemOptions.CurrentEmbedObjects[iParamIdx].Count; iPropIdx++)
            //{
              returnString += sEmbedActive + "\t[AfrsDO(\"" + SystemOptions.CurrentEmbedObjects[iParamIdx][2] + "\", typeof(DO" +
                           SystemOptions.CurrentEmbedObjects[iParamIdx][2] + "), IsList = " + bIsList.ToString().ToLower() + ")]";
            //}
          }
          else if (SystemOptions.CurrentEmbedObjects[iParamIdx][5] == "true")
          {
            if (SystemOptions.CurrentEmbedObjects[iParamIdx][5] == "true")
              bIsList = true;
            else
              bIsList = false;

            if (SystemOptions.CurrentEmbedObjects[iParamIdx][6] == "false")
            {
              sEmbedActive = "";
            }
            else
            {
              sEmbedActive = "//";
            }

            if (returnString.Length > 0) returnString += "\r\n";
            //for (int iPropIdx = 0; iPropIdx < SystemOptions.CurrentEmbedObjects[iParamIdx].Count; iPropIdx++)
            //{
            returnString += sEmbedActive + "\t[AfrsDO(\"" + GetPluralName(SystemOptions.CurrentEmbedObjects[iParamIdx][2]) + "\", typeof(DO" +
                         SystemOptions.CurrentEmbedObjects[iParamIdx][2] + "), IsList = " + bIsList.ToString().ToLower() + ")]";
            //}
          }
        }
      }
      return returnString;
    }
  
    private string GetDOPrivateVariableList()
    {
      string embeddedObj = "";
      string returnString = "";
      string dataTypeLength = "";
      string sEmbedActive = "";
      string line = "";

      returnString += "\t#region DO Variables";

      for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentTableFields.Count; iParamIdx++)
      {
        if (SystemOptions.CurrentTableFields[0] != null)
        {
          string columnName = SystemOptions.CurrentTableFields[iParamIdx][0];
          string friendlyFieldName = GetFriendlyFieldName(columnName);
          string fieldType = "";
          //if (bool.Parse( SystemOptions.CurrentTableFields[iParamIdx][4]))
          //  fieldType = GetAppDataType("bigint", dataTypeLength);
          //else
            fieldType = GetAppDataType(SystemOptions.CurrentTableFields[iParamIdx][1], dataTypeLength);

          if (bool.Parse(SystemOptions.CurrentTableFields[iParamIdx][7].ToString()))
          {
            line = "\t\tprivate OptionalRetrieveParam<" + fieldType + "> m_" + friendlyFieldName + " = new OptionalRetrieveParam<" + fieldType + ">();";
          }
          else
          {
            line = "\t\tprivate " + fieldType + " m_" + friendlyFieldName + ";";
          }

          if (returnString.Length > 0) returnString += "\r\n";
          returnString += line;
        }
      }

      returnString += "\r\n\t#endregion";

      //returnString += "\r\n\r\n\t#region Embedded Data Object Variables\r\n";

      //if (SystemOptions.CurrentEmbedObjects != null)
      //{
      //  for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentEmbedObjects.Count; iParamIdx++)
      //  {
      //    if (SystemOptions.CurrentEmbedObjects[iParamIdx][6] == "false")
      //    {
      //      sEmbedActive = "";
      //    }
      //    else
      //    {
      //      sEmbedActive = "//";
      //    }
          
      //    if (SystemOptions.CurrentEmbedObjects[iParamIdx][4] == "true")
      //    {
      //      if (embeddedObj.Length > 0) embeddedObj += "\r\n";
      //      embeddedObj += sEmbedActive + "\t\tprivate DO" + SystemOptions.CurrentEmbedObjects[iParamIdx][2] + " m_" + SystemOptions.CurrentEmbedObjects[iParamIdx][2] + ";";
      //    }
      //    else if (SystemOptions.CurrentEmbedObjects[iParamIdx][5] == "true")
      //    {
      //      if (embeddedObj.Length > 0) embeddedObj += "\r\n";
      //      embeddedObj += sEmbedActive + "\t\tprivate AfrsDataObjectList<DO" + SystemOptions.CurrentEmbedObjects[iParamIdx][2] + "> m_" + GetPluralName(SystemOptions.CurrentEmbedObjects[iParamIdx][2]) + ";";
      //    }
      //  }
      //}

      //embeddedObj += "\r\n\t#endregion";

      returnString += embeddedObj;
      return returnString;
    }

    private string GetDOPropertyList()
    {
      string returnString = "";
      string searchResultAttrib = "";
      int iSearchResultCounter = 1; // Don't change this. It MUST seed from 1, not 0. 
                                    // Otherwize standard lookup DO's will cause prms search 
                                    // page to fall over. The AfrsStandardLookupDataObject-
                                    // Base class contains a Search Result attribute with a 
                                    // resultIndex of 0 on the Description property, which
                                    // must display before any other columns in the grid.
      string sEmbedActive = "";
      string sEmbedParentAttrib = "";
      string sSearchParametersAttrib = "";
      string sOverride = "";

      returnString += "#region DO Properties";

      for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentTableFields.Count; iParamIdx++)
      {
        string dbType = "";
        string fieldType = "";

        if (SystemOptions.CurrentTableFields[0] != null)
        {
          string propertyTemplate = "";

          if (SystemOptions.CurrentTableFields[iParamIdx][9] == "True")
          {
						sEmbedParentAttrib = "\r\n\t\t[AfrsDOParentDependent(\"Id\", typeof(DO|PARENTTYPE|))]\t\t";
          }
          else
          {
            sEmbedParentAttrib = ""; ;
          }

          if (Convert.ToBoolean(SystemOptions.CurrentTableFields[iParamIdx][5]))
          {
            sSearchParametersAttrib = "\r\n\t\t[AfrsDOSearchParameter]";
          }
          else
          {
            sSearchParametersAttrib = "";
          }

          //if (Convert.ToBoolean(SystemOptions.CurrentTableFields[iParamIdx][4]))
          //{
          //  propertyTemplate = GetTemplateText("DOPublicIDProperty.txt");
          //  dbType = GetSystemDataType("bigint");
          //  fieldType = GetAppDataType("bigint", "");
          //}
          //else
          //{
          propertyTemplate = GetTemplateText("DOPublicProperty.txt");
            sOverride = GetPublicOverrideValue(SystemOptions.CurrentTableFields[iParamIdx][0],m_TableName.Replace("Description",""));
            dbType = GetSystemDataType(SystemOptions.CurrentTableFields[iParamIdx][1]);
            fieldType = GetAppDataType(SystemOptions.CurrentTableFields[iParamIdx][1], "");
          //}

          //[AfrsSearchResult(1, 80, DbType.Int32)]
          if (SystemOptions.CurrentSearchResults != null && SystemOptions.CurrentSearchResults.Count > 0)
          {
            for (int iSearchResultIdx = 0; iSearchResultIdx < SystemOptions.CurrentSearchResults.Count; iSearchResultIdx++)
            {
              if (SystemOptions.CurrentSearchResults[iSearchResultIdx][0] == SystemOptions.CurrentTableFields[iParamIdx][0])
              {
								searchResultAttrib = "[AfrsSearchResult(" + iSearchResultCounter + ", 100, " + dbType + ")]\r\n\t\t";
                iSearchResultCounter++;
                break;
              }
              else
              {
                searchResultAttrib = ""; ;
              }
            }
          }

          //dbType = GetSystemDataType(dbType);//dbType.Replace("System", "DbType");
          string friendlyFieldName = GetFriendlyFieldName(SystemOptions.CurrentTableFields[iParamIdx][0]);
          

          string deleteatt = "";
          //if (chkDelete.Checked)
          //{
          //  deleteatt = "\r\n\t\t[DataDelete(true)]";
          //}

          string smandatory = "true";
          if (Convert.ToBoolean(SystemOptions.CurrentTableFields[iParamIdx][4]) || SystemOptions.CurrentTableFields[iParamIdx][0].ToString().IndexOf("Active") > 0)
          {
            smandatory = "false";
          }
          propertyTemplate = propertyTemplate.Replace("|DOSEARCHPARAMETERATRIB|", sSearchParametersAttrib);
          propertyTemplate = propertyTemplate.Replace("|SEARCHRESULTATTRIB|", searchResultAttrib);
          propertyTemplate = propertyTemplate.Replace("|EMBEDDEPENDANT|", sEmbedParentAttrib.Replace("|PARENTTYPE|", friendlyFieldName.Substring(0, friendlyFieldName.Length - 2)));
          propertyTemplate = propertyTemplate.Replace("|FIELDFRIENDLYNAMEWITHSPACES|", GetFriendlyFieldNameWithSpaces(SystemOptions.CurrentTableFields[iParamIdx][0]));
          propertyTemplate = propertyTemplate.Replace("|FIELDFRIENDLYNAME|", friendlyFieldName);
          //propertyTemplate = propertyTemplate.Replace("|UPPERFIELDFRIENDLYNAME|", friendlyFieldName.ToUpper());
          propertyTemplate = propertyTemplate.Replace("|DBTYPE|", dbType);
          propertyTemplate = propertyTemplate.Replace("|OVERRIDE|", sOverride);
          if (bool.Parse(SystemOptions.CurrentTableFields[iParamIdx][7].ToString()))
          {
            string sTempFieldType = "OptionalRetrieveParam<" + fieldType + ">";
            propertyTemplate = propertyTemplate.Replace("|FIELDTYPE|", sTempFieldType);
          }
          else
          {
            propertyTemplate = propertyTemplate.Replace("|FIELDTYPE|", fieldType);
          }

          propertyTemplate = propertyTemplate.Replace("|MANDATORY|", smandatory);
          propertyTemplate = propertyTemplate.Replace("|DELETEATTRIBUTE|", deleteatt);

          returnString += "\r\n" + propertyTemplate;
        }
      }

      returnString += "\t\t#endregion";

      //returnString += "\r\n\r\n" + "\t\t#region Embedded Data Object Properties\r\n";

      //string embeddedDOProperties = "";

      //if (SystemOptions.CurrentEmbedObjects != null)
      //{
      //  for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentEmbedObjects.Count; iParamIdx++)
      //  {
      //    if (SystemOptions.CurrentEmbedObjects[iParamIdx][6] == "false")
      //    {
      //      sEmbedActive = "";
      //    }
      //    else
      //    {
      //      sEmbedActive = "//";
      //    }

      //    //if (SystemOptions.CurrentEmbedObjects[iParamIdx][7] == "false")
      //    //{
      //    //  sEmbedParent = "";
      //    //}
      //    //else
      //    //{
      //    //  sEmbedParent = "\r\n\t\t|ACTIVE|[AfrsDOParentDependent(\"Id\")]";
      //    //}

      //    if (SystemOptions.CurrentEmbedObjects[iParamIdx][4] == "true")
      //    {
      //      string propertyTemplate = GetTemplateText("DOPublicEmbedProperty.txt");
      //      string dbType = GetSystemDataType("int");
      //      ////dbType = GetSystemDataType(dbType);//dbType.Replace("System", "DbType");
      //      string friendlyFieldName = GetFriendlyFieldName(SystemOptions.CurrentEmbedObjects[iParamIdx][2]);
      //      //string fieldType = SystemOptions.CurrentEmbedObjects[iParamIdx][1];
      //      propertyTemplate = propertyTemplate.Replace("|FIELDFRIENDLYNAMEWITHSPACES|", GetFriendlyFieldNameWithSpaces(SystemOptions.CurrentEmbedObjects[iParamIdx][2]));
      //      propertyTemplate = propertyTemplate.Replace("|FIELDFRIENDLYNAME|", friendlyFieldName);
      //      propertyTemplate = propertyTemplate.Replace("|UPPERFIELDFRIENDLYNAME|", friendlyFieldName.ToUpper());
      //      propertyTemplate = propertyTemplate.Replace("|DBTYPE|", dbType);
      //      propertyTemplate = propertyTemplate.Replace("|DESTINATIONPROPERTY|", friendlyFieldName);
      //      propertyTemplate = propertyTemplate.Replace("|EMBEDOBJECT|", SystemOptions.CurrentEmbedObjects[iParamIdx][2]);
      //      //propertyTemplate = propertyTemplate.Replace("|EMBEDPARENT|", sEmbedParent);
      //      propertyTemplate = propertyTemplate.Replace("|ACTIVE|", sEmbedActive);
            

      //      if (embeddedDOProperties.Length > 0) embeddedDOProperties += "\r\n";
      //      embeddedDOProperties += propertyTemplate.TrimEnd("\r\n\n".ToCharArray());
      //    }
      //    else if(SystemOptions.CurrentEmbedObjects[iParamIdx][5] == "true")
      //    {
      //      string propertyTemplate = GetTemplateText("DOPublicEmbedListProperty.txt");
      //      string dbType = GetSystemDataType("int");
      //      ////dbType = GetSystemDataType(dbType);//dbType.Replace("System", "DbType");
      //      string friendlyFieldName = GetFriendlyFieldName(SystemOptions.CurrentEmbedObjects[iParamIdx][2]);
      //      //string fieldType = SystemOptions.CurrentEmbedObjects[iParamIdx][1];
      //      propertyTemplate = propertyTemplate.Replace("|FIELDFRIENDLYNAMEPLURALWITHSPACES|", GetFriendlyFieldNameWithSpaces(GetPluralName(friendlyFieldName)));
      //      propertyTemplate = propertyTemplate.Replace("|FIELDFRIENDLYNAMEPLURAL|", GetPluralName(friendlyFieldName));
      //      propertyTemplate = propertyTemplate.Replace("|FIELDFRIENDLYNAME|", friendlyFieldName);
      //      propertyTemplate = propertyTemplate.Replace("|UPPERFIELDFRIENDLYNAME|", friendlyFieldName.ToUpper());
      //      propertyTemplate = propertyTemplate.Replace("|DBTYPE|", dbType);
      //      propertyTemplate = propertyTemplate.Replace("|DESTINATIONPROPERTY|", friendlyFieldName);
      //      propertyTemplate = propertyTemplate.Replace("|EMBEDOBJECT|", SystemOptions.CurrentEmbedObjects[iParamIdx][2]);
      //      //propertyTemplate = propertyTemplate.Replace("|EMBEDPARENT|", sEmbedParent);
      //      propertyTemplate = propertyTemplate.Replace("|ACTIVE|", sEmbedActive);

      //      if (embeddedDOProperties.Length > 0) embeddedDOProperties += "\r\n";
      //      embeddedDOProperties += propertyTemplate.TrimEnd("\r\n\n".ToCharArray());
      //    }
      //  }
      //}

      //returnString += embeddedDOProperties + "\r\n";
      //returnString += "\t\t#endregion";

      return returnString;
    }

    private string GetDOClearVariableList()
    {
      string returnString = "";
      string sEmbedActive = "";

      for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentTableFields.Count; iParamIdx++)
      {
        if (SystemOptions.CurrentTableFields[0] != null)
        {
          string dbType = SystemOptions.CurrentTableFields[iParamIdx][1];
          string friendlyFieldName = GetFriendlyFieldName(SystemOptions.CurrentTableFields[iParamIdx][0]);
          string fieldType = SystemOptions.CurrentTableFields[iParamIdx][1];
          string systemFieldType = GetAppDataType(SystemOptions.CurrentTableFields[iParamIdx][1], SystemOptions.CurrentTableFields[iParamIdx][2]);
          string val = ClearDataType(dbType);
          string line = "";
          if (bool.Parse(SystemOptions.CurrentTableFields[iParamIdx][7].ToString()))
          {
            line = "\tthis.m_" + friendlyFieldName + " = new OptionalRetrieveParam<" + systemFieldType + ">();";
          }
          else
          {
            line = "\tthis.m_" + friendlyFieldName + " = " + val + ";";
          }
          if (returnString.Length > 0) returnString += "\r\n";
          returnString += line;
        }
      }
      if (SystemOptions.CurrentEmbedObjects != null)
      {
        for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentEmbedObjects.Count; iParamIdx++)
        {
          if (SystemOptions.CurrentEmbedObjects[iParamIdx][6] == "false")
          {
            sEmbedActive = "";
          }
          else
          {
            sEmbedActive = "//";
          }

          if (SystemOptions.CurrentEmbedObjects[iParamIdx][4] == "true")
          {
            string dbType = GetSystemDataType("int");
            string friendlyFieldName = GetFriendlyFieldName(SystemOptions.CurrentEmbedObjects[iParamIdx][2]);

            string line = "\r\n" + sEmbedActive + "\tif(this.m_" + friendlyFieldName + " != null) { this.m_" + friendlyFieldName + ".Clear(); }";
            returnString += line;
          }
          else if (SystemOptions.CurrentEmbedObjects[iParamIdx][5] == "true")
          {
            string dbType = GetSystemDataType("int");
            string friendlyFieldName = GetFriendlyFieldName(SystemOptions.CurrentEmbedObjects[iParamIdx][2]);

            string line = "\r\n" + sEmbedActive + "\tif(this.m_" + GetPluralName(friendlyFieldName) + " != null) { this.m_" + GetPluralName(friendlyFieldName) + ".Clear(); }";
            returnString += line;
          }
        }
      }

      return (returnString);
    }

    private string CamelCase(string Original)
    {
      string sReturnString = "";
      char[] sChars = Original.ToCharArray();
      for (int i = 0; i < sChars.Length; i++)
      {
        if (i == 0)
          sReturnString = sChars[i].ToString().ToUpper();
        else
          sReturnString += sChars[i].ToString();
      }
      return sReturnString;
    }

    private string CapFirstLetter(string Original)
    {
      string sReturnString = "";
      char[] sChars = Original.ToCharArray();
      for (int i = 0; i < sChars.Length; i++)
      {
        if (i == 0)
          sReturnString = sChars[i].ToString().ToUpper();
        else
          sReturnString += sChars[i].ToString().ToLower();
      }
      return sReturnString;
    }

    private void CreateDOPartialForLookupDescription()
    {
      string spText = "";
      string sParent = m_TableName.Replace("Description", "");

      spText = GetTemplateText("DOStandardLookUpDescriptionPartialClassTemplate.txt");
      string inheritValue = GetInheritValue();

      #region Set Namespace on DO

      spText = spText.Replace("|NAMESPACE|", txtNSDO.Text);

      #endregion

      spText = spText.Replace("|INHERIT|", inheritValue);
      spText = spText.Replace("|PARENTCLASS|", sParent);
      spText = spText.Replace("|TABLEFRIENDLYNAMEWITHSPACES|", GetFriendlyFieldNameWithSpaces(sParent));
      spText = spText.Replace("|TABLENAME|", m_TableName);

      txtDOPartial.Text = spText;
    }
    #endregion

    #region Unit Tests
		private void CreateUnitTest()
		{
			string spText = "";

			spText = GetTemplateText("CDOPrmsUTTemplate.txt");

			#region Set Namespace on Unit Test

			spText = spText.Replace("|NAMESPACE|", txtNSUT.Text);

			#endregion

			string lowerTableName = m_TableName[0].ToString().ToLower() + m_TableName.Substring(1);

			spText = spText.Replace("|TABLENAME|", m_TableName);

			spText = spText.Replace("|PARAMLIST|", GetUnitTestVariableList());

			spText = spText.Replace("|CREATEVALUES|", GetUnitTestCreateVariableList(lowerTableName));

			spText = spText.Replace("|TESTRETRIEVEWITHEMBEDDEDOBJECT|", GetUnitTestEmbeddedObject());
			
			txtUnitTest.Text = spText;
		}

    private string GetUnitTestVariableList()
    {
      string returnString = "";

      for (int iParamIdx = 0; iParamIdx < 1; iParamIdx++)
      {
        if (SystemOptions.CurrentTableFields[0] != null)
        {
          string columnName = SystemOptions.CurrentTableFields[iParamIdx][0];

          string friendlyFieldName = GetFriendlyFieldName(columnName);

          string variableName = friendlyFieldName;

          string line = "";

          // Old code for generating default values
          //if(SystemOptions.CurrentTableFields[iParamIdx][1] == "int" || SystemOptions.CurrentTableFields[iParamIdx][1] == "bigint")
          //  line = "\t\tthis.m_" + m_TableName + ".DataObject." + variableName + " = 0;";
          //else if(SystemOptions.CurrentTableFields[iParamIdx][1] == "bit")
          //  line = "\t\tthis.m_" + m_TableName + ".DataObject." + variableName + " = false;";
          //else
          //  line = "\t\tthis.m_" + m_TableName + ".DataObject." + variableName + " = \"" + m_TableName + "Test String " + iParamIdx.ToString() + "\";";

          string dbType = SystemOptions.CurrentTableFields[iParamIdx][1].ToLower();
          string defaultValue = null;

          switch (dbType)
          {
            case "tinyint":
            case "smallint":
            case "int":
            case "bigint":
              {
                defaultValue = "10";
                break;
              }

            case "binary":
            case "varbinary":
            case "image":
            case "text":
            case "ntext":
              {
                defaultValue = "new byte[] { 1, 2, 3 }";
                break;
              }

            case "bit":
              {
                defaultValue = "false";
                break;
              }

            case "varchar" : 
            case "nvarchar":
              {
                defaultValue = "\"Test " + variableName + "\"";
                break;
              }
            
            case "char" : 
            case "nchar" :
              {
                defaultValue = "\'b\'";
                break;
              }

            case "datetime": 
            case "smalldatatime":
            case "timestamp":
              {
                defaultValue = "DateTime.Now";
                break;
              }

            case "float":
            case "decimal":
						case "money":
						case "smallmoney":
							{
								defaultValue = "12.3M";
								break;
							}

            case "numeric":
            case "real":
              {
                defaultValue = "12.3F";
                break;
              }

            case "uniqueidentifier":
              {
                defaultValue = "Guid.NewGuid()";
                break;
              }

            default :
              {
                defaultValue = "null";
                break;
              }
          }

          line = "        this.m_" + m_TableName + ".DataObject." + variableName + " = " + defaultValue + ";";

          returnString += line + "\r\n";
        }
      }
      return (returnString);
    }

    private string GetUnitTestCreateVariableList(string smallTableName)
    {
      string returnString = "";

			for (int iParamIdx = 1; iParamIdx < SystemOptions.CurrentTableFields.Count; iParamIdx++)
			{
				if (SystemOptions.CurrentTableFields[0] != null)
				{
					string columnName = SystemOptions.CurrentTableFields[iParamIdx][0];

					string friendlyFieldName = GetFriendlyFieldName(columnName);

					string variableName = friendlyFieldName;

					string line = "";

          // Old code for generating default values
          //if(SystemOptions.CurrentTableFields[iParamIdx][1] == "int" || SystemOptions.CurrentTableFields[iParamIdx][1] == "bigint")
          //  line = "\t\tthis.m_" + m_TableName + ".DataObject." + variableName + " = 0;";
          //else if(SystemOptions.CurrentTableFields[iParamIdx][1] == "bit")
          //  line = "\t\tthis.m_" + m_TableName + ".DataObject." + variableName + " = false;";
          //else
          //  line = "\t\tthis.m_" + m_TableName + ".DataObject." + variableName + " = \"" + m_TableName + "Test String " + iParamIdx.ToString() + "\";";

          string dbType = SystemOptions.CurrentTableFields[iParamIdx][1].ToLower();
          string defaultValue = null;

          switch (dbType)
          {
            case "tinyint":
            case "smallint":
            case "int":
            case "bigint":
              {
                defaultValue = "10";
                break;
              }

            case "binary":
            case "varbinary":
            case "image":
            case "text":
            case "ntext":
              {
                defaultValue = "new byte[] { 1, 2, 3 }";
                break;
              }

            case "bit":
              {
                defaultValue = "false";
                break;
              }

            case "varchar":
            case "nvarchar":
              {
                defaultValue = "\"Test " + variableName + "\"";
                break;
              }

            case "char":
            case "nchar":
              {
                defaultValue = "\'b\'";
                break;
              }

            case "datetime":
            case "smalldatatime":
            case "timestamp":
              {
                defaultValue = "DateTime.Now";
                break;
              }

            case "float":
            case "decimal":
            case "money":
						case "smallmoney":
							{
								defaultValue = "12.3M";
								break;
							}

            case "numeric":
            case "real":
              {
                defaultValue = "12.3F";
                break;
              }

            case "uniqueidentifier":
              {
                defaultValue = "Guid.NewGuid()";
                break;
              }

            default:
              {
                defaultValue = "null";
                break;
              }
          }

          line = "      this.m_" + m_TableName + ".DataObject." + variableName + " = " + defaultValue + ";";
          returnString += line + "\r\n";
				}
			}
      return (returnString);
    }

    private string GetUnitTestEmbeddedObject()
    {
      string returnString = "";

      if (SystemOptions.CurrentEmbedObjects != null)
      {
        for (int iParamIdx = 0; iParamIdx < SystemOptions.CurrentEmbedObjects.Count; iParamIdx++)
        {
          if (SystemOptions.CurrentEmbedObjects[0] != null)
          {
            string returnMethod = "";

						// DataObject
						if (SystemOptions.CurrentEmbedObjects[iParamIdx][4] == "true")
						{
							returnMethod = GetTemplateText("UTEmbeddedDOMethod.txt");
							int ind = returnMethod.IndexOf("|TABLENAME|");
							returnMethod = returnMethod.Replace("|TABLENAME|", m_TableName);
							returnMethod = returnMethod.Replace("|EMBEDDEDOBJECT|", SystemOptions.CurrentEmbedObjects[iParamIdx][2]);
						}
						
						// Lists
            if (SystemOptions.CurrentEmbedObjects[iParamIdx][5] == "true")
            {
              returnMethod = GetTemplateText("UTEmbeddedListMethod.txt");
              int ind = returnMethod.IndexOf("|TABLENAME|");
              returnMethod = returnMethod.Replace("|TABLENAME|", m_TableName);
              returnMethod = returnMethod.Replace("|EMBEDDEDOBJECT|", GetPluralName(SystemOptions.CurrentEmbedObjects[iParamIdx][2]));
            }

            if (SystemOptions.CurrentEmbedObjects[iParamIdx][6] == "true")
            {
              returnMethod = returnMethod.Replace("|ACTIVE|", "//");
            }
            else
            {
              returnMethod = returnMethod.Replace("|ACTIVE|", "");
            }

            returnString += returnMethod;
          }
        }
      }

      return (returnString);
    }

    #endregion

    #region Clear Methods
    private void ClearTextFields()
    {
      txtSPCreate.Text = "";
      txtSPDelete.Text = "";
      txtSPRetrieve.Text = "";
      txtSPRetrieveAll.Text = "";
      txtSPUpdate.Text = "";
      txtDO.Text = "";
      txtDOPartial.Text = "";
      txtUnitTest.Text = "";
    }
    #endregion

    private void frmMain_FormClosed(object sender, FormClosedEventArgs e)
    {
      if (!reloadingForm)
      {
        Application.Exit();
      }
    }

    #endregion

    private void button1_Click(object sender, EventArgs e)
    {
      this.StoredProcName = Microsoft.VisualBasic.Interaction.InputBox("Stored Procedure Name and Parameters", "Stored Procedure","",100,100);
      string sTableName = Microsoft.VisualBasic.Interaction.InputBox("Table name (DO Name)", "Table Name","",100,100);

      LoadFromStoredProc(this.StoredProcName,sTableName);

      //frmSearchColumns loadForm = new frmSearchColumns();
      //loadForm.TableColumns = sourceColumns;
      //loadForm.StoredProcName = sStoredProcName;
      //loadForm.returnForm = this;
      //loadForm.TableName = "ReportsFoldersReadyForMergingSummary";
      //loadForm.ActiveConn = PRMSSQLConn;
      //loadForm.ShowDialog();
      //if (this.m_DOOptions == null)
      //  MessageBox.Show("Error Setting DO Options");
      //else

      //if (this.m_SelectedTableColumns != null)
      //{
      //  btnReGenerate.Enabled = true;
      //}
      //else
      //{
      //}

      lblTableCounter.Visible = false;
      progressBarTables.Visible = false;
      flowLayoutPanel1.Visible = true;

      btnPreview.Visible = true;
      btnGenerate.Visible = true;
      btnReGenerate.Visible = true;

      //frmSearchColumns searchColumns = new frmSearchColumns();
      //searchColumns.StoredProcName = sStoredProcName;

      //searchColumns.Owner = this;
      //searchColumns.ShowDialog();
    }

    public DataSet ExecuteDataSetSQL(string sExecuteSQLString, SqlConnection activeConn)
    {
      DataSet returnDS = new DataSet();
      DataTable dataTable = new DataTable();

      SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sExecuteSQLString, activeConn);

      sqlDataAdapter.Fill(dataTable);

      returnDS.Tables.Add(dataTable);

      return returnDS;
    }

    //private void btnMerge_Click(object sender, EventArgs e)
    //{
    //  //frmMergeDO mergeForm = new frmMergeDO();
    //  //if (this.RaptorSQLConn == null)
    //  //{
    //  //  SetRaptorDBConn();
    //  //}
    //  //mergeForm.ActiveConn = this.RaptorSQLConn;
    //  //mergeForm.ShowDialog();
    //}
  }
}
